Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 
like image 760
user3399326 Avatar asked Jun 25 '15 14:06

user3399326


People also ask

How do you drop a trigger in a table?

Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to delete. Expand Triggers, right-click the trigger to delete, and then click Delete. In the Delete Object dialog box, verify the trigger to delete, and then click OK.

Which is an another method to drop a trigger in SQL?

Remarks. You can remove a DML trigger by dropping it or by dropping the trigger table.

How do I delete an existing trigger in MySQL?

To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema: mysql> DROP TRIGGER test.


1 Answers

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; 
like image 116
Solomon Rutzky Avatar answered Oct 05 '22 14:10

Solomon Rutzky