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
CREATE TRIGGER `DateCreatedTriggerTS` BEFORE INSERT ON `products`
FOR EACH ROW UPDATE products SET DateEntered = NOW( ) WHERE ProductID = NEW.ProductID
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.