On Aug 21, 2005, at 9:55 AM, <[EMAIL PROTECTED]> wrote:
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.
well, while true, neither you nor your application actually will be
setting the value of the insert_ts at all. It is the database that will
be doing that for you. By defining a column as
foo DATETIME DEFAULT CURRENT_TIMESTAMP
and then _not_ explicitly INSERTing any value, you have automagically
stored your logic for the insert_ts right in the database by letting
the db do it for you. The DATETIME column type, and the DEFAULT value
facility are provided for a purpose. Use them. If the database
erroneously starts inserting typos like '2909-11-31' by itself then the
SQLite team have bigger problems on their hand.
Now you are left to worry about only the last_upd_ts.
You can now update that column either with an AFTER UPDATE TRIGGER or
within the application.
While I understand the wisdom of pushing as much data-related logic in
the database, you have the application layer for some reason after all.
You are, after all, doing an UPDATE from the application, which, you
want, then to kick-start the TRIGGER. Hence, simply doing a
UPDATE table
SET last_upd_ts = '<put your validated current timestamp here>'
WHERE condition
is not that revolutionary.
--
Puneet Kishor
