> can someone explain to me why folks try to accomplish the above using > TRIGGERs when the insert time can be set so easily by simply defining > the column as > > insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP > > and last_upd_ts (or its equivalent) can set in the application logic > and updated during an UPDATE.
In practice, I have found it to be more effective to keep data-related logic in sql rather than in any external application. Triggers are great for enforcing these types of rules. The problem with using table defaults is that it does not enforce any type of rule, sql will accept any value I give it, like '2909-12-25', for the insert_ts field, rather than forcing it to be the actual time of insertion. > As far as I can see, both TRIGGERs above will only happen after INSERT, > and therefore, will insert exactly the same time in both insert_ts and > last_upd_ts defeating the intended purpose of the two fields. That's what I would expect them to do. The problem is that no time gets inserted into insert_ts. > Perhaps you want to create one AFTER INSERT and one AFTER UPDATE > TRIGGER. I have an AFTER UPDATE trigger, which doesn't do me much good if the insert trigger isn't working. Bill
