CODEWITHSUNDEEP
mysqltriggerssql-update
Mukund
Mukund

Reputation: 1105

how to get the updated row from a table when calling a trigger after update

This is my table structure

mysql> select * from product_table;
+----------------+---------------------+
| product_number | product_description |
+----------------+---------------------+
|              1 | product one         |
|              2 | product two         |
|              3 | product three       |
|              9 | product five        |
|             10 | product six         |
|             11 | product six         |
+----------------+---------------------+

I want to say update a row say 9th product number from the table like this

UPDATE product_table SET product_description ="product seven" WHERE product_number=9;

So that in the trigger i can get the corresponding updated product number from the table product_table

I wrote the trigger like this and it got created without any errors.

DELIMITER //
CREATE TRIGGER product_table_update 
      AFTER UPDATE
       ON product_table
       FOR EACH ROW
BEGIN


       DECLARE l_product_number INT;
    set @l_table_name = 'product_table';
    set @l_action = 'updation';


              SET @l_table_column = 'product_description';
select new.product_number into @l_product_number from product_table;//  here is i think where the problem is , i am trying to fetch the updated row to l_product_number

       IF (OLD.product_description <> NEW.product_description) THEN
         SET @oldval = OLD.product_description;
         SET @newval = NEW.product_description;
         select concat(@oldval,@newval) into @l_description;

       END IF;

       INSERT INTO audit_table_test
       ( table_name,
      changed_row_id,
      action,
      table_column,
      change_desciption,
      change_time
     )
       VALUES
   ( @l_table_name,
      @l_product_number,
      @l_action,
      @l_table_column,
      @l_description,
     NOW()
     );
END; //
DELIMITER ;

then when i tried to update like this 

UPDATE product_table SET product_description ="product seven" WHERE product_number=11;

This error is showing 

ERROR 1172 (42000): Result consisted of more than one row

I know the problem has to be in this code

select new.product_number into @l_product_number from product_table;//  here is i think where the problem is , i am trying to fetch the updated row to l_product_number

Please someone help me to get the update row on calling this trigger

Upvotes: 0

Views: 49

Answers (1)

wchiquito
wchiquito

Reputation: 16551

Try:

...
 SET @l_table_column = 'product_description';

 /*
 here is i think where the problem is , i am trying to fetch the
 updated row to l_product_number
 */
 -- select new.product_number into @l_product_number from product_table;
 SET @l_product_number := NEW.product_number;

 IF (OLD.product_description <> NEW.product_description) THEN
...

Upvotes: 1

Related Questions