Mysql Trigger IF target field changed

Question

I would like to fire an UPDATE trigger only if a certain fields has been modified. Is it possible to do with a if statement; execute a particular query.

Exemple (Not working as shown):

DROP TRIGGER IF EXISTS `catch_mytable_update`//
CREATE TRIGGER `catch_mytable_update` AFTER UPDATE ON `mytable`
 FOR EACH ROW BEGIN

IF (old.name != new.name) THEN

DELETE FROM trigger WHERE tablename='mytable' AND rowid = new.id;
INSERT INTO trigger (tablename, rowid, cmd, cmduser, time) VALUES('mytable', new.id, 'UPDATE', CURRENT_USER(), NOW());

ENDIF//

END//

Delimiter: //

Mark White · Accepted Answer · 2012-03-31 07:51:02Z

1

Try changing:

    IF (name.old != name.new) THEN

to:

    IF old.name != new.name THEN

edited Mar 31, 2012 at 7:51

answered Mar 31, 2012 at 7:12

Mark White

461 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Louis Over a year ago

Sorry, yes are right. This was a spelling mistake when I typed the question. I tried the change, and I am still getting an error :(

Mark White Over a year ago

We'll need to know the MySQL error code or message for us to help :)

Louis Over a year ago

Here is the following error I get when I run the query mentioned: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9

Mark White Over a year ago

Also, what is the table that's named 'trigger' actually used for?

Louis Over a year ago

The table trigger is used for monitoring triggers occurring in several tables. This query already works fine with no IF statement. thx

|

Collectives™ on Stack Overflow

Mysql Trigger IF target field changed

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related