Help with a trigger or something like that...

[Aptiva Dave]

I am building a web app that allows people to apply for jobs at my company. Everything that the user submits in the app is placed in a SQL 2005 database. One of the requirements is that all information in the applicant information is held for at least one year. In my main table which holds the primary id for the applicant I have an entry date field that is populated with the getDate() function whenever a new applicant is added to the system. Also, to keep from having duplicate records of an applicant in the system, the web app calls a stored procedure that deletes all of the applicant's info from the database based on the appID number (the month and day of the applicant's birthday combined with the last four of their SSN). Is there any kind of trigger or other SQL programming that I can put into place that will check the database daily and delete all of the applicant data that has been in the database for over a year?

[David Mujica]

While in the SQL Server Management Studio logged in as "sa", look under SQL server Agent and you will see a spot where you can schedule jobs on the server. Create a job that does your cleanup and schedule it when you want it to run and you're done. :thumbsup:

[Aptiva Dave]

Okay, I can that, but how do I call my stored proc and loop through all of the apps that are over a year old? My Stored Proc is called DEL_App and it has a parameter of @appID, which is an integer. Now, I have created this select statement that will pull all of the apps that are over a year old:

select appID from applicant where Year(entryDate) < Year(getDate())

How can I loop through these apps and delete them using my stored proc?

[_Damian S_]

Aptiva Dave wrote:

select appID from applicant where Year(entryDate) < Year(getDate())

DANGER!! Deleting items that are returned by this select query will delete every app entered prior to the current year... while this seems like what you want, it's not. For instance... say I enter 100 apps into your system on 31/12/2009. Then on 1/1/2010 the scheduled job runs... voila!! 100 apps that are only a day old are deleted!!!

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[Tripathi Swati]

hi, you can use job schedule or else can put trigger on your applicant table which will fire after insert. now use

select datediff(year,entryDate,getdate())

coz ur query will remove all records which are less than getdate()..

Reasons are not Important but Results are Important. Swati Tripathi

[Ashfield]

Tripathi Swati wrote:

put trigger on your applicant table which will fire after insert

Bad idea. What if it suddenly has to delete several thousand records? The delay will be significant. This sort of clean up should be done as a scheduled job, preferably out of hours and just after a backup, just in case anything goes wrong.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Tripathi Swati]

yes, do agree that job scedule is better option but how come trigger will fire on delete also because i told trigger on after insert ???

Reasons are not Important but Results are Important. Swati Tripathi

[Ashfield]

The trigger will not fire on delete, but will attempt to delete records when it is fired by an insert. If there are lots of records to delete (i.e. old records) then there could be a significant delay

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Aptiva Dave]

Alright, how do I pull all of the appIDs from the select statement into the Stored Proc that I have already built to delete items? The stored proc only accepts one parameter, which is a single appID.