john john - 16 days ago 5
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?

trigger

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

Answer

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.

Comments