I am trying to write my first Triggers, but am having trouble understanding when the Triggers should fire (and the related syntax to make them work).
All of the examples online show a Timestamp being added BEFORE a record is Inserted or Updated, but I would think you would want to do that as the very last thing before the record is “committed” to the database (i.e. after the Insert or Update).
For instance, you could be in a Transaction and start to Insert/Update a record, but not “Commit” the transaction until an hour later?!
So who cares if you started the Transaction at 2:00 p.m., you didn’t finish things up until you performed the “Commit” at 3:00 p.m. which is the time that the Timestamp should reflect!!
Does that make sense?
Here is an Update Trigger from online…
DELIMITER $$
CREATE TRIGGER trig_test_bu BEFORE UPDATE ON test
FOR EACH ROW BEGIN
SET NEW.created_at = OLD.created_at;
SET NEW.changed_at = CURRENT_TIMESTAMP;
END
$$
DELIMITER ;
I would like to re-write this so that it updates the Timestamp immediatelyafter it updates the record.
Personally I try hard to avoid triggers. There is no fun like the good time you have trying to clean up a database where a demented mutant trigger code went wild
So who cares if you started the Transaction at 2:00 p.m., you didn’t finish things up until you performed the “Commit” at 3:00 p.m. which is the time that the Timestamp should reflect!!
In that case you need to do the update immediately before the commit. The timestamp will always reflect the time of update regardless of whether you have a before or after trigger.
It is more efficient to do this type of operation in a before trigger, as in an after trigger you cannot change the new variable but you must do an actual update statement which may lead to a recursive invovation of the trigger. (I don’t know how this is implemented in Mysql.)
I’m starting to think MySQL is pretty primitive in the area of Triggers, which is disappointing.
Hope MySQL really beefs up their support of Stored Procedures and Triggers in the next version. As it stands, they are rudimentary at best, and pretty limited from what you can do in SQL Server or Oracle.
