December 2, 2003 at 10:20 am
I would like to create a trigger to delete a record after 2 days from the day that it is inserted based on the status of a certain field. ex. When a user completes the first step in the app a record is inserted into a status table and the status field is set to active. When a user completes the app the status table is set to complete. I only want to give a user 2 days to complete the app and if this does not happen I want to delete the record. Can I write a trigger to do that after Insert?
Also is it possible to have a trigger write text to a field. ex. When a record is inserted have a trigger write "you have x days before this record expires"
December 2, 2003 at 2:02 pm
It appears that you have an interface where the user reviews the records that have been inserted app. Rather than writing the message into the data itself, you may find if more helpful to have the interface just read the inserted date and indicate something like "you have x days before this record expires".
The actual deletion process is probably set as a recurring job (some stored procedure). I assume you have it reading the creation date and the status.
Could you please clarify the problem? The trigger will not be able to delete the record, but can help getting data set correctly so that another routine can properly perform the deletion.
What am I missing here?
Guarddata-
December 2, 2003 at 2:13 pm
I am tracking the start date and you did not miss much. I guess I need to figure out how to write a stored proceedure to handle this task. Is there a tut that is worth reading? I think the biggest problem I will have writing the SP is how to declair the start date and dictate the date for deletion. If someone could help me there I should be able to handle the rest. Thanks
December 2, 2003 at 2:33 pm
Like a lot of things...it depends.
For example, if all you need is to delete the entry after 48 hours if the status is active, you can create a stored procedure
CREATE PROCEDURE DeleteOldActives AS
DECLARE @delDate DATETIME
SET @selDate = DATEADD( DAY, -2, GETDATE())
DELETE FROM table1 WHERE Status = 'Active' AND DateCreated < @delDate
RETURN
Create a job that runs this script every 10 or 15 minutes and you are fine.
The CreateDate column could just be defined to have a default value of GETDATE().
In this case, you don't even need any triggers.
If you need more than that, I would recommend any SQL book to help get you started.
Guarddata-
December 2, 2003 at 2:39 pm
Thanks guarddata I will give it a try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply