Strictly Programming

MySql trigger to update fields on the own table

Posted in MySql by sqllyw on 04/14/2008

MySql’s trigger confusion…


Often we need to update a field in the table which is just updated, the immediate impression is we have to use a ‘after update’ trigger:
#1

Create Trigger `update_flag` After Update on `sales` for each row BEGIN 
  set new.flag = new.flag2;
END;

this will not compile as ‘new’ can not be updated in the ‘After Update’ trigger. so we ended up:
#2

Create Trigger `update_flag` After Update on `sales` for each row BEGIN 
  update table sales set flag = flag2 where id = new.id;
END;

but you will get a complain:
“can not update table performer in trigger because it is already used by statement which invoked this trigger”

actually situation like this requires a ‘before update’ trigger:
#3

Create Trigger `update_flag` Before Update on `sales` for each row BEGIN
	set new.flag = new.flag2;
END;

Conclusion: to update the same table where trigger issues, use ‘before update’ so that you ‘new’ to use, if to update fields in other table then you can use #2

Advertisements
Tagged with: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: