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

Answer

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;