October 11, 2002 at 6:41 am
is it possible to set up a trigger for a select command? If so how?
I dont know anything about these things at all could someone show me the syntax?
What i want to do is basically delete messages whose expiration date (contained within the table and set through an asp form)
is... well expired.
Thanks!
Edited by - szopdog on 10/11/2002 06:45:41 AM
October 11, 2002 at 7:24 am
No only, DELETE, INSERT, and UPDATE. You can however create a trace to watch for selects but it does add a bit to the server load (not a lot just a bit). Depending on the version of SQL if it is a 2000 server you can output the data to a table and since it is being inserted you can put a trigger on that table, but you will have to parse the data and haven't even looked at it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 10/11/2002 07:25:16 AM
October 11, 2002 at 7:32 am
ok thanks. That's getting a bit too involved.
Like i said i dont really know anything about sql other then simple select update delete insert statements.
October 11, 2002 at 7:33 am
"What i want to do is basically delete messages whose expiration date (contained within the table and set through an asp form)
is... well expired."
I do audits of old data in my db too. The question I have is why are you thinking of using triggers for this? I used triggers on INSERT & UPDATE when people put in bad credit card numbers and such. For old data, you can just set up a stored proc to DELETE anything < getdate() and a job in SQL agent to run it every night.
October 11, 2002 at 7:41 am
great idea nickel! One problem though...
i have no clue where to even start with a thing like that!
If you are willing to help, i'll try it that way.
October 11, 2002 at 8:58 am
I agree with nickel01. Triggers have their place, but I don't believe thats one of them...
I perform archive and cleanup functions through an hourly job (requirements from business logic make it neccessary for me to have more up to date data than nightly. At first, I believed this would put too much of a load on the server, but the reduced number of rows affected with this, offsets the frequency.)
Just my opinion...
If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....
October 11, 2002 at 11:46 am
szopdog,
The easiest way to set up a job is to use Enterprise Manager. Expand your server, expand Management, expand SQL Server Agent. Right-click on Jobs, select New Job. Build the job.
To delete old records try this:
DELETE tablename
WHERE datecolumn < getdate()
That will delete anything older than today (be careful with this. getdate() also returns the time. So it will delete anything older than the minute it is run.)
You can delete older days by changing the getdate() function.
getdate() is this moment
getdate()-1 is this moment a day ago
getdate()-2 is this moment two days ago.
Test the getdate function in Query Analyzer.
SELECT getdate()
SELECT getdate()-1
SELECT getdate()-10
-Also, look in the Books OnLine for more information.
-Bill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply