On Tue, Mar 29, 2011 at 11:32 AM, Simon Slavin <slav...@bigfraud.org> wrote: > 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 way to handle the absence of IF here is this: CREATE TRIGGER ... BEGIN INSERT INTO changeLog SELECT ... WHERE OLD.name IS NOT NEW.name; -- or INSERT INTO changeLog SELECT ... WHERE OLD.name IS NOT NEW.name AND ...; ... END; The trick is that you use an INSERT...SELECT/UPDATE/DELETE statement with a WHERE clause where a conjunction with an expression unrelated to the row being inserted/updated/deleted is used. That expression is likely, and certainly can be, completely unrelated to the rows being inserted/updated/deleted -- but it should be related to the event that triggered the trigger. Sure, you can get a very similar effect with a WHEN clause on a trigger, but a) you'll need more triggers, b) you get a fairly artificial constraint on the log schema (OK, not really, but it requires clever trigger body writing to make the WHEN approach not constrain the log schema), c) using a WHEN clause is an uncomfortable straightjacket compared to using WHERE clauses on the INSERT/UPDATE/DELETE statements in the trigger body. > 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. You can't have an INSERT/UPDATE/DELETE embedded in a larger SELECT, which means that you can't use CASE for making conditional changes. The way to make conditional changes is with WHERE clauses (see above) (or do it at the application layer, of course). Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
