October 14, 2009 at 5:48 am
I would like now how to create a trigger only if it does not exists.
The below code gives me an error as follows:
The code:
IF NOT EXISTS (select * from sys.trigger)
BEGIN
CREATE TRIGGER [INS_Form]
ON [dbo].[Sys_Forms] FOR INSERT AS
BEGIN
//Triger code
END
END
ERROR:
Incorrect syntax near the keyword 'TRIGGER'.
October 14, 2009 at 2:51 pm
IF EXISTS (select * from sysobjects where name like '%reminder2%')
DROP TRIGGER reminder2
go
CREATE TRIGGER reminder2
ON Sales.Customer FOR INSERT AS
BEGIN
select 1
END
Try this...
October 14, 2009 at 4:50 pm
Rups (10/14/2009)
IF EXISTS (select * from sysobjects where name like '%reminder2%')DROP TRIGGER reminder2
go
CREATE TRIGGER reminder2
ON Sales.Customer FOR INSERT AS
BEGIN
select 1
END
Try this...
Rups,
I'm curious about whether there's a particular reason we'd use the LIKE with wildcards to check for the presence of a particular name (where name like '%reminder2%'). Why not just look for equality (where name = 'reminder2')?
Rubeesh, I'm sure you can see how to adapt Rups' code to create the trigger when it doesn't already exist and not dropping it if it's there. Your situation may actually benefit from the twist he (or she?) put on the process.
Also, it's my understanding that although the SQL 2000 system resource names, including "sysobjects", are still supported in SQL 2005, MS recommends using the newer schema names like "sys.objects". For this query, it would be a simple substitution ... from sys.objects where name like '%reminder2%'but one should be aware that some other columns have new names as well ("objectID" rather than just "ID", for instance.
====
edit: added note about modifying to not drop existing object.
October 14, 2009 at 11:10 pm
Thank You very much. It worked
October 15, 2009 at 7:33 am
Thanks John for that correction.,
I was checking randomly for trigger on my sample database and had that like operator and forgot to remove it prior to posting it here.
-RP
October 19, 2009 at 8:28 am
Here is another way to go. It avoids the DROP which I think affects any auditing done on your DDL. Of course it does the 1 time creation of a "dummy" in order to allow the ALTER. But since it only happens once versus the drop happening every time you change the code I find it preferable.
IF OBJECT_ID(N'[dbo].[A_Trigger]') IS NULL
BEGIN
EXEC ('CREATE TRIGGER
[dbo].[A_Trigger]
ON [dbo].[A_Table]
AFTER INSERT
AS BEGIN SELECT ''STUB'' END');
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[A_Trigger]
ON [dbo].[A_Table]
AFTER INSERT
AS
BEGIN
--code
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply