Embedding CREATE TRIGGER statement help

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 2005 Forums

Transact-SQL (2005)

Embedding CREATE TRIGGER statement help

Author

Topic

kryptos
Starting Member

1 Post

Posted - 2009-03-25 : 02:40:20

Hi all.

I am a newbie to SQL Server having mostly worked on BD2 and ORACLE in the past. I need to create an after insert trigger in a SQL script. That is no problem, but what I also want to do, as this will be part of an install, is to check if the script has already been run on the DB by checking an entry in a DB table and if so, create the trigger and then insert a value into this scipt log table.

The script:
IF NOT EXISTS
(
SELECT * FROM dbo.UpdateScriptLog
WHERE ScriptName = 'Update 2009-03-24 15h30 AS.sql'
)
BEGIN
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

CREATE TRIGGER insertIDToProcess
ON dbo.Recordings
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID as int;
SET @ID = (select ID from inserted );
insert into dbo.ProcessingQueue values (@ID);
END

INSERT INTO dbo.UpdateScriptLog ([ScriptName], [Script])
VALUES ('Update 2009-03-24 15h30 AS.sql','Applied')
COMMIT
END

However when I run this I get:
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'TRIGGER'.

This syntax logic works perfectly for a CREATE TABLE statement in the exact place of the CREATE TRIGGER statement above, but when I put a CREATE TRIGGER between BEGIN and END it throws this error.

Syntax to simply replace the trigger with the same name would also suffice.

Any help will be very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 14:23:54

you cant create trigger conditionally. what you need may be this




CREATE TRIGGER insertIDToProcess
ON dbo.Recordings
AFTER INSERT
AS 
BEGIN
IF NOT EXISTS 
(
SELECT * FROM dbo.UpdateScriptLog 
WHERE ScriptName = 'Update 2009-03-24 15h30 AS.sql'
)
BEGIN
BEGIN TRANSACTION trig1
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON;


insert into dbo.ProcessingQueue 
select ID from inserted 

INSERT INTO dbo.UpdateScriptLog ([ScriptName], [Script])
VALUES ('Update 2009-03-24 15h30 AS.sql','Applied')
COMMIT TRANSACTION trig1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources