Help with scheduled triggers and SQL Server 2005 1

[TeaAddictedGeek]

Ok, SQL gurus, I need you. My skills are okay but this is something a bit out of my usual scope:

In SQL Server 2005, I need to be able to create a trigger that runs every two hours and does a insert into table1 select statement from table2 then deletes from table2. The guts of what this has to do is cake and easily done within a normal stored proc; I've just never done triggers in SQL that I can recall. In fact, I think the last one I may have worked on was back in SQL Server 7.0.

Thanks in advance!

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[ptheriault]

You can't schedule are trigger. You would need to create a stored procedure and run it from a scheduled job with the SQL Agent.

Triggers are just what the name implies. They are code that is fired when some event (that you define) on the table is triggered.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[TeaAddictedGeek]

This is what comes of proj requirements being dictated by someone who clearly doesn't work with SQL Server too much. THANK YOU. I thought I was losing my mind.

Is creating a scheduled job easy to do?


"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[ptheriault]

Yes, very simple.
From SSMS, open SQL SErver Agent -> Jobs.
Right Click on Jobs and select new job.
In the General tab give it a name and I usually change the owner to sa. Type your description then click on the steps page on the left hand side.
Select new
Enter a step name
select the database your procedure resides in.
Enter the syntax to execute your proc.
Like: EXEC MyProc <input?>
Click ok
Then select the schedules page.
Create a new schedule to run every 2 hours.
If you have database mail setup you may want to set up a notification to email you if your job fails.
Click ok to save your job.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[TeaAddictedGeek]

Here's another good question: can you temporarily disable a scheduled job? That way I could start it up when everything was ready to go.

And thanks a bunch for your help!!

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[ptheriault]

Yes, you can. Just right click on the job after you created it and select disable/enable.

- Paul

- If at first you don't succeed, find out if the loser gets anything.