CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqltriggers
user3399326
user3399326

Reputation: 973

drop trigger if exists and create

I would like to check if the trigger exists on [tbl] and create another one. I tried it this way but didn't work. What am I doing wrong?

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = '[dbo].[trg]' AND [type] = 'TR')
      DROP TRIGGER [dbo].[trg] ON [dbo].[tbl]
GO
CREATE TRIGGER [dbo].[trg] ON [dbo].[tbl] 
AFTER DELETE
AS
BEGIN
   //
END
GO

Upvotes: 38

Views: 84911

Answers (4)

IamJose
IamJose

Reputation: 111

In case you use SQLite use this syntax:

DROP TRIGGER IF EXISTS trigger_name;

More info here: https://www.sqlitetutorial.net/sqlite-trigger/

Upvotes: -1

Neshta
Neshta

Reputation: 2745

If you use SQL Server 2016, you can use a shorter variant.

DROP TRIGGER IF EXISTS [dbo].[trg]

https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql

Upvotes: 22

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

The [name] field in sys.objects will contain only the actual name (i.e. trg), not including the schema (i.e. dbo in this case) or any text qualifiers (i.e. [ and ] in this case).

AND, you don't specify the table name for DROP TRIGGER since the trigger is an object by itself (unlike indexes). So you need to remove the ON clause (which is only used with DDL and Logon triggers).

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'trg' AND [type] = 'TR')
BEGIN
      DROP TRIGGER [dbo].[trg];
END;

Please note that you should prefix the object name string literal with an N since the [name] field is a sysname datatype which equates to NVARCHAR(128).

If you did want to incorporate the schema name, you could use the OBJECT_ID() function which does allow for schema names and text qualifiers (you will then need to match against object_id instead of name):

IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[trg]')
               AND [type] = 'TR')
BEGIN
      DROP TRIGGER [dbo].[trg];
END;

And to simplify, since the object name needs to be unique within the schema, you really only need to test for its existence. If for some reason a different object type exists with that name, the DROP TRIGGER will fail since that other object is, well, not a trigger ;-). Hence, I use the following:

IF (OBJECT_ID(N'[dbo].[trg]') IS NOT NULL)
BEGIN
      DROP TRIGGER [dbo].[trg];
END;

Upvotes: 72

mxix
mxix

Reputation: 3659

Can you try

SELECT * FROM sys.objects WHERE [name] = PARSENAME('[dbo].[trg]',1) AND [type] = 'TR'

EDIT:

Well srutzky already gave you the answer and well explained, you can parse the name with the help of PARSENAME.

Upvotes: 0

Related Questions