Need timed event to delete items with NULL value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Need timed event to delete items with NULL value

Author

Topic

ice-computer
Starting Member

4 Posts

Posted - 2007-05-12 : 08:14:46

I am generally a C programmer and have little experience with DB programming, so I apologize right from the start.

I have a table that allows a registration item with a verification item that will be NULL when the item is created. What I wish to do is delete these if the verification item is still NULL after a specified time (say 24 to 48 hours).

I would prefer to do this with some sort of trigger in one of two ways and any suggestions are much appreciated.
1. Have a stored procedure or function (not sure which is best to utilize) that runs every 2 days which checks the table for the NULL values and deletes any older than 48 hours.
2. Create a stored procedure or function when the registration is made that will check if the verification is still NULL for that particular record 24 hours later.
I would think the first would be the simplest, but am not certain.

Again, forgive my inexperience with DB programming and again I appreciate any advice given.

Thanks,
Steven Henley

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-12 : 08:28:31

Well, you have to Create the stored procedure.. and specify your bussiness logic into it..

Then you require to create the SQL Job, which will execute that procedure after every 2 days, and will do necessary deletion from the table..

Here is the link, how can you create the job in SQL SERVER 2005

http://msdn2.microsoft.com/en-us/library/ms190268.aspx

Hope this much information will give you a kick start.. !!!

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2007-05-12 : 11:46:29

You will need to have a column in the record that stores the date/time when the record was created - you can apply a Default Constraint to the column so that the current date/time (i.e. GetDate()) is provided automatically.

The you need to schedule some SQL to remove the "stale" ones. You could wrap this up in an SProc, but you could also just schedule the raw snippet of SQL code:


DELETE D
FROM MyTable AS D
WHERE     MyVerificationItem IS NULL
      AND MyCreateDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate())-2, 0)

The DATEADD/DATEDIFF stuff gives midnight, day-before-yesterday

Not sure why you would schedule it to run every 48 hours, as that would leave items up to 4 days old ... but that part is flexible so you can schedule it as often as you wish!

Kristen

ice-computer
Starting Member

4 Posts

Posted - 2007-05-12 : 13:30:30

I truly appreciate the quick response Chirag and Kristen.

I really just had not dealt with sqlserver enough to know how to set up a timed event, so I appreciate the link very much Chirag.

I had forgotten to mention that I had such a column Kristen but thanks much for the code snippet as I had not looked at the time diff part at this point.

With my previous jobs there were guys who handled the DB stuff (assuming I could describe the data characteristics that I needed well enough). You never realize how much you depend on the DB weenies until you don't have any.

Thanks again,
Steven

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-13 : 04:19:20

You never realize how much you miss being called a DB weenie until you don't have anyone to do it.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources