Autodelete from Database

[daNewfie]

I was wondering how I autodelete an item from my sql database using coldfusion. for intstance, I have two types of entries in the table, one item should be autodeleted after 30 days and the second item is autodeleted after 90 days and the 90 day item may also have a pic uploaded as well and I want to get rid of that too.


Thanks in advance
Craig

 

[GUJUm0deL]

daNewfie, well, my personal opinion is to never delete anything from the dB. First off, deleting entries screws up the Primary ID key, and as time goes by it'll be harder to remember what item was deleted and why. What I always do is have a bit column field called "isLive". If the item should be visible make the bit field 1, and if its not visible or holds no purpose anymore change it to 0. But that's me...ok, back to your question.

I belive you can do using a stored procedure that will check the item's datetime stamp (assuming you have one) against today's date, and if it over 30 days (or 90) you can execute a delete statement.

I think this will shed some more light on your question:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[SpotsyRWH]

What I do is setup a cold fusion scheduled task to run once a day that checks for all the items needing to be deleted based on a date field I record for each record.

 

[imstillatwork]

what version of mysql?

 

[daNewfie]

I understand that the deletion thing may mess up the primary Id but It is a classified ad database and I dot want more there than needs to be. and once its gone I will never need it..

Im using mssql 2000

Craig

 

[GUJUm0deL]

daNewfie, are you using MySQL or MS SQL? Those are two different things.

In any case, what you want is doable. what I would do is have a stored procedure that will check the item's datetime stamp (assuming you have one) against today's date, and if it over 30 days (or 90) you can execute a delete statement.

Something like:

DELETE FROM myTest
WHERE  (myDate < GETDATE() - 30)

The above would delete from the table TableName where the datstamp is gretaer the 30 days (or 90)

As always, TEST!

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[daNewfie]

MS SQL

and thanks