Keeping Number of Rows Fixed

[NormRumac]

I use one of the tables in my database as a log of user transactions.

I don't want table growing indefinitely large, so I was wondering if I can prespecify a way keeping the table fixed at say 100 rows. In other words, once the table is filled with the a 100 rows, the next inserted row will replace the 1st row that was inserted into the table...and so on.

If this is not possible, I imagine I could write an sql script which could run, say weekly, and remove all rows from this table, except the most recent 100. What is the best way for me to do this?

Thanks,
--Norm

 

[RileyCat]

You could do something in a trigger. The trigger would fire every time an insert was about to be posted and if it exceeded the number of rows allowed, either the trigger or your insert statement would manipulate the data such that the new row essentially replaced the first row. A FIFO type of thing.

 

[donutman]

If the front-end app is the only way records will get added, then I'd tell it to run a SP to delete the oldest record after each insert. Populate the log with the max number of rows (using dummy data). Better to avoid triggers.
-Karl

 

[NormRumac]

Yes, surprisingly this is not the 1st time i've been told to stay away from triggers. I guess it can get kind of messy if one is not careful...

Thanks,
--Norm

 

[donutman]

And if you are using disconnected recordsets in your app, then the insert could be done with a SP that includes the delete. Thereby making light work of using "best practices".
-Karl