Results 1 to 11 of 11
  1. #1
    Join Date
    May 2009
    Posts
    7

    Unanswered: Tricky trigger question

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    7
    I'm certainly interested, though I must warn you I have no money to spend on additional software

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    7
    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....

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by attrcat
    well, the only problem with this solution is that it uses up too much disk space.
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Posts
    7
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't see why you insist on splitting up a table when you don't have to

    good luck with your project

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  10. #10
    Join Date
    May 2009
    Posts
    7
    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?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by attrcat
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •