So, I am creating a new database, and want to set up triggers to track row counts in each table as rows are inserted or deleted.
A couple of questions -- 1. Do I have to create a separate pair of AFTER INSERT and AFTER DELETE triggers for each table that I want to track, or is there a way to create a generic trigger that fires whenever any table is touched and returns the table name and the number of rows affected? 2. Doing a trigger like so is fine sqlite> create trigger del_foo after delete on foo ...> begin ...> update counts set rows = rows - 1 where tablename = 'foo'; ...> end; however, what if I DELETE FROM foo WHERE msg LIKE 'sqlite%' How do I get the number of rows that were deleted by the above DELETE? Do I first do a SELECT to find out the number of matches before doing the DELETE? 3. When doing a batch of INSERTs in a transaction, will the trigger fire after the transaction is committed, or on every update? I am assuming the former, but, in that case, how will know how many rows were inserted? Any other gotchas? -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
