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.
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
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....
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