MySQL After Insert and After Update Trigger

Question

Hi!

I have two tables t1 and t2. i have created two triggers tr1 after insert on t1 and tr2 after update on t2. in both the tables i m updating table t2. so, it is throwing an error saying the t2 table is already getting updated in another trigger so can not update it again.

Please let me know if anyone has faced this type of problem and fixed it.

Thanks in advance! MySQL DBA.

Answer 1

If you want to modify the data that is being updated or inserted, you should use a BEFORE UPDATE and/or BEFORE INSERT trigger and then make use of NEW alias (it references the row, that is being inserted or the row as it will look after the update is applied) - you can actually modify the fields that are being inserted. OLD alias references the row before the update is applied or the row before it is deleted (in triggers that fire on delete).

So probably for trigger tr2 you need to something along the lines of:

DELIMITER $$
    CREATE TRIGGER tr2 BEFORE UPDATE ON t2
    FOR EACH ROW BEGIN
      SET NEW.field1 = some_value_you_want_to_set_it_to;
    END;
$$
DELIMITER ;

Because in a trigger you can not actually UPDATE a table, that is being already updated, thats right.

Answer 2

Hi All,

I have fixed this problem, there was a problem as i was trying to update and insert on same table tb1 from two triggers but Mysql does not allowed the same table to be modified i suppose.

Thanks