john john - 1 year ago 81
MySQL Question

MySQL trigger doesn't work on table insert

Maybe I'm just being dense but hopefully someone can help. I am trying to add a trigger for a DateCreated column, which fires when a row is added. When I try to add I get

Can't update table 'products' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

I realize from this answer that it is because MySQL won't let me trigger after the insert because of a recursive loop, but my trigger has the update BEFORE and I'm using NEW. what am I missing out my trigger? Also, if I wanted to do a DateModified what would I have to alter?


CREATE TRIGGER `DateCreatedTriggerTS` BEFORE INSERT ON `products`
FOR EACH ROW UPDATE products SET DateEntered = NOW( ) WHERE ProductID = NEW.ProductID

Answer Source

Is ProductID a unique key in this products table? If so, your UPDATE is basically updating only the row that spawned the trigger, right?

So it's simpler to do this:

CREATE TRIGGER `DateCreatedTriggerTS` BEFORE INSERT ON  `products` 
FOR EACH ROW SET NEW.DateEntered = NOW( );

That will change the value of the DateEntered column only in the row that spawned the trigger.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download