Using EXISTS with ALTER TRIGGER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using EXISTS with ALTER TRIGGER

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-07 : 08:57:39

Todd writes "I need to ALTER a TRIGGER's functionality, but only if it exists, otherwise, I need to create it. I get errors everytime I run the script.

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'MyTrigger')
ALTER TRIGGER MyTrigger ON MyTable FOR INSERT, UPDATE
AS
IF SomeCondition
BEGIN
-- Trigger Logic
END
ELSE
CREATE TRIGGER MyTrigger ON MyTable FOR INSERT, UPDATE
AS
IF SomeCondition
BEGIN
-- Trigger Logic
END
GO

What is wrong with code? Is this type of functionality NOT supported by SQL Server? Please help.

Thanks,
Todd"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-07 : 09:03:06

try this:


IF EXISTS(SELECT name FROM sysobjects WHERE name = 'MyTrigger')
begin
	exec('ALTER TRIGGER MyTrigger ON MyTable FOR INSERT, UPDATE
	AS
	IF SomeCondition = 1
	BEGIn
	-- Trigger Logic
	END')
end
ELSE
begin
	exec('CREATE TRIGGER MyTrigger ON MyTable FOR INSERT, UPDATE
		AS
		IF SomeCondition = 1
		BEGIN
		-- Trigger Logic
		END')
end

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-01-08 : 06:51:17

Can I suggest


IF EXISTS(SELECT name FROM sysobjects WHERE name = 'MyTrigger')
begin
	exec('DROP TRIGGER MyTrigger')
end
ELSE
begin
	exec('CREATE TRIGGER ...')
end

I'd hate to have to maintain the trigger code in two places!

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-08 : 07:00:39

Shouldn't have the else in that statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-01-08 : 07:40:42

Mea culpa, Thanks Nigel

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources