On 9/22/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > 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.
This is very cool. But, this raises another question (for my edification, natch). What if the transaction fails and the db rolls back? Do the triggered updates of the counts table also roll back? I am assuming "yes" but, that is, oh, so clever, so I want to confirm. > > HTH > Dennis Cote > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
