Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: Making a row delete itself at a certain time?

    Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

    Also, I have another question:
    when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:

    Code:
    PK-----------Item----------price
    1------------XYZ1----------$$$$
    2------------XYZ2----------$$$$
    3------------XYZ3----------$$$$
    
    when I delete PK 2 this is what happens
    
    PK-----------Item----------price
    1------------XYZ1----------$$$$
    3------------XYZ3----------$$$$
    
    the PKs are not in consecutive order
    
    
    What I want is this to happen: when PK 2 is deleted I need it to show as follows
    
    PK-----------Item----------price
    1------------XYZ1----------$$$$
    2------------XYZ3----------$$$$
    
    The PKs stay in consecutive order.
    See how the PK are still in consecutive order?
    Last edited by Luke101; 12-02-06 at 17:52.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Luke101
    I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application.
    Why delete it at all? Save the expiration date within your row, and then create a view on that table with something like WHERE expiration_date <= current_date. Then make your application use the view to retrieve the data (or simply add the condition to your application logic), thus it will never see expired rows. If you put an index on that column the retrieval should be quite fast.

    If you are concerned about space, than run a batch job every night that deletes the expired rows.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you could create an agent job that would delete the rows also.

Posting Permissions

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