Hi all,

I am trying to figure out a way to dynamically manage the amount of entries in two different tables. I think a trigger with a stored procedure would be the best way to do this, but I can't figure out how to best write it.

What I need to have happen is the following:

I have two tables of information. The first table should only hold one day's worth of information, and the second should hold three month's worth of information. I want to design a trigger that takes any entry in the first table that is over a day old and deletes it, inserting the values into the historical table. I want to do a similar thing with the historical table, deleting any entry once it becomes more than 3 months old.

I have a field called "timestamp", so I can compare this against the current date/time if that sounds reasonable.

Can anyone figure this out? Thanks in advance

that's ~so~ inefficient, excessively complex, error-prone, and needless

i have a better solution that does not involve a trigger, nor a stored procedure, nor any additional effort on your part whatsoever, and yet it can still deliver the exact same benefits as what you're trying to do

would you be interested?

I'm certainly interested, though I must warn you I have no money to spend on additional software

well, this will be good news for you then, because there is no additional software required either

the solution? it's actually quite simple

forget about the separate table for one day's worth of information, and the other table for three month's worth

just have a single table which collects all your information indefinitely

there, wasn't that easy?

no stored proc, no trigger, no development effort, no extra software...

neat, eh?

well, the only problem with this solution is that it uses up too much disk space. That was the reason for only storing 3 months worth of historical data in the first place, I guess I should have said that....

i disagree, it does not

although i suppose it depends on what you mean by "too much"

how many rows do you expect to collect in 3 months? in 3 years? in 3 decades?

make realistic estimates, please

also, what kind of data are we talking about here? banking transactions? friends of friends? football pool bets? urls scraped off teh interwebs? license plates of cars that you've seen?

There will be an entry made to the tables once every two seconds for each person on a team, with 10 fields per entry. A year of this would be a HUGE amount of data. Also, it is necessary to have a table with JUST one day's worth of data for analysis

sorry, i don't see why you insist on splitting up a table when you don't have to

good luck with your project

I have 100Mb of data.
I am going to split it into 10 chunks of 10Mb.

I still have 100Mb of data!

You design is not saving any diskspace whatsoever. Rudy is quite correct in his suggestions of using a single table.

OK, well forget about the two different tables plan...what if I just had one table, and I wanted it to be truncated at 3 months worth of data. How could I accomplish this?

one way would be to copy the last 3 months' worth of data into a new table, delete the old one, and rename the new one

but i say it once again, you do ~not~ need to do this