Using a SQL Server Agent Scheduled job to recreate dropped trigger

[ajaeger]

I have a trigger on one of my tables. When the administrator updates the table from within the software, it drops the trigger on that table. As a solution, I want to have a "create trigger" statement scheduled to run periodically in the SQL Server Agent. Most of the time when it runs, however, the trigger will exist. I'm trying to add an "if not exists" statement at the start but am having no luck:

If not exists
(Select * From dbo.sysobjects where NAME = 'UpdateMeetFunc' and TYPE='TR')
Create trigger [dbo].[UpdateMeetFunc]
On [dbo].[Orders]
After Insert, Update As
Begin;with cte as
(select Inserted.Order_Number, dbo.FuncGetOrdersDesc(Inserted.Order_Number) as Updated_Value from Inserted)
update Order_Meet
Set ADDITIONAL_BADGES = cte.Updated_Value
from Order_Meet inner join cte on Order_Meet.Order_Number = cte.Order_Number
End

I am getting the error "Incorrect syntax near the keyword 'trigger'."

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[gmmastros]

Create trigger needs to be the first thing in a batch.

Try this:

If not exists
(Select * From dbo.sysobjects where NAME = 'UpdateMeetFunc' and TYPE='TR')
[!]exec ('[/!]Create trigger [dbo].[UpdateMeetFunc]
On [dbo].[Orders]
After Insert, Update As
Begin;with cte as 
(select Inserted.Order_Number, dbo.FuncGetOrdersDesc(Inserted.Order_Number) as Updated_Value from Inserted)
update Order_Meet
Set ADDITIONAL_BADGES = cte.Updated_Value 
from Order_Meet inner join cte on Order_Meet.Order_Number = cte.Order_Number
End[!]')[/!]

DDL Triggers were first introduced in SQL2005. If you are using that version (or newer), you may want to do a little research on [google]sql server ddl triggers[/google]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom