P Kishor wrote: > 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,
Yes. > 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? > No. > 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? You don't need to the trigger fires for each row that is deleted, so you always delete 1. > > 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? > Same for the insert trigger. It executes after each row is inserted. Always add 1. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
