On 29 Mar 2011, at 4:12pm, Sutter, Doug wrote:
> I know how to create a unique trigger for each column where I hard-code
> the column's name as shown below. But I would like to create a trigger
> that would fire when any column is updated and log the specific details
> for that column only.
You can compare the old and new values for any column you name (old.columnName
and new.columnName) but a single UPDATE command can change any number of
columns, so you would have to specifically test for each column inside your
TRIGGER, something like
CREATE TRIGGER myTabUpdated
AFTER UPDATE OF myTab
FOR EACH ROW BEGIN
IF old.name <> new.name THEN
INSERT INTO changeLog ...;
IF old.address <> new.address THEN
INSERT INTO changeLog ...;
...
END;
Except that there is no such command as 'IF' in SQL. Each TRIGGER can have
only one conditional: a WHEN clause that can go before the BEGIN.
The UPDATE OF colName ON tableName syntax, and the FOR EACH ROW WHEN ... BEGIN
syntax both mean you don't have to try specifying conditionals inside your
TRIGGER.
You might be able to use CASE to do what you want but you will still need to
use several statements in a row, and I'm drawing a blank right now on how it
could be done.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
