Only ALTER a TRIGGER if is exists

Question

I am implementing an auditing system on my database. It uses triggers on each table to log changes. I need to make modifications to these triggers and so am producing ALTER scripts for each one. What I'd like to do is only have these triggers be altered if they exist, ideally like so:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'MyTable_Audit_Update')
BEGIN
ALTER TRIGGER [dbo].[MyTable_Audit_Update] ON [dbo].[MyTable]
 AFTER Update
...
END

However when I do this I get an error saying "Invalid syntax near keyword TRIGGER"

The reason that these triggers may not exist is that auditing can be enabled/disabled on tables which the end user can specify. This involves either creating or dropping the triggers. I am unable to make the changes to the triggers upon creation as they are dynamically created and so I must still provide a way altering the triggers should they exist.

Thanks, but that gives an error saying it can't find the trigger. — GarethV, Aug 06 '14 at 09:40

HoneyBadger · Accepted Answer · 2014-10-20T15:20:11.413

10

The alter statement has to be the first in the batch. So for sql server it would be:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'MyTable_Audit_Update')
BEGIN
EXEC('ALTER TRIGGER [dbo].[MyTable_Audit_Update] ON [dbo].[MyTable]
 AFTER Update
...')
END

edited Oct 20 '14 at 15:20

answered Aug 06 '14 at 09:53

HoneyBadger

14,750
3
34
48

3

Just as a side note, I would write the trigger out first, test it and then wrap it with the EXEC as trying to debug within an EXEC() can be difficult – twoleggedhorse Aug 06 '14 at 10:08

score 2 · Answer 2 · edited May 23 '17 at 11:45

2

This might be a similar issue to one that I found with Sybase years ago, where I found that when trying to execute a create table statement conditionally, the DDL is executed prior to assessing the conditional statement. The only workaround was to use execute immediate.

edited May 23 '17 at 11:45

Community

1
1

answered Aug 06 '14 at 09:47

ninesided

23,085
14
83
107

score 1 · Answer 3 · answered Aug 06 '14 at 10:55

Unlike CREATE TRIGGER, I failed to find a reference that explicitly states that

CREATE TRIGGER must be the first statement in the batch

but it seems that this restriction applies to ALTER TABLE too.

The simple way to do this would be to DROP the TRIGGER and re-create it:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'MyTable_Audit_Update')
     DROP TRIGGER MyTable_Audit_Update 
GO

CREATE TRIGGER [dbo].[MyTable_Audit_Update] ON [dbo].[MyTable]
AFTER Update

...
END

Only ALTER a TRIGGER if is exists

3 Answers3