• Welcome, Guest. Please login.
 

How do I create a trigger to keep tally of the number of records?

Started by Andrew Lindsay, December 14, 2009, 10:22:08 pm

Previous topic - Next topic

Andrew Lindsay

Hello,

I have an extremely large table (over 19,000,000 entries).  I would like to have a quick way to keep tally of the numbers of records.  I have tried 'Select Count(*) from tTable;' but this takes nearly 5 minutes to recover the number of records.

I would like to set up a second table called 'Status', with a single entry called Records, that would be incremented when a record is added, and decremented when a record is deleted.

I have read that triggers can be set up to modify a figure in another table, but I am not sure how to do this for inserts or deletes.  Any assistance would be appreciated.

Best regards

Andrew Lindsay

Rolf Brandt

Hello Andrew,

you could add an extra table 'Status'  to your database with one field 'Records'.
Add one record with the actual number of records in your table to it, lets say: 9,124,877.
Next you create two triggers with the names 'OnInsert' and 'OnDelete'. You can do it with this with the followin SQL syntax:

CREATE TRIGGER [OnInsert]
AFTER INSERT
ON parts
FOR EACH ROW
BEGIN
UPDATE Status SET Records = Records + 1 WHERE Records >= 0;
END

CREATE TRIGGER [OnDelete]
AFTER DELETE
ON parts
FOR EACH ROW
BEGIN
UPDATE Status SET Records = Records - 1 WHERE Records >= 0;
END

You would need to change the name of the table. I tried this on Fred's sample database on the table parts.

Works excellent and (SQ)Litening fast!
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Andrew Lindsay