1

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
Community
  • 1
  • 1
john
  • 775
  • 3
  • 15
  • 31
  • Stop for a moment and ask yourself, "where will the record pointer be after an update statement and where does it need to be to execute an insert trigger" and you'll understand why you cannot do this. If that doesn't help, ask yourself, "how is the table, row, or index locked for an insert vs an update?" TLDR: You can't do this. Try it using an AFTER trigger and you might get away with it. – Chris Caviness Nov 18 '16 at 19:28

2 Answers2

0

INSERT AFTER. Even if this did work, you'd be at risk of changing all the other rows, then having your insert fail due to a constraint violation.

Chris Caviness
  • 586
  • 3
  • 10
0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828