21

I need to delete a trigger in SQL Server. Seems like it should be simple enough, but because there is a thing called a "delete trigger", a trigger that is invoked upon deletion, it seems impossible to find resources on how to actually delete an already existing trigger.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Teekin
  • 12,581
  • 15
  • 55
  • 67
  • Just make sure you aren't dropping a trigger that is needed for data integrity. Dropping a trigger because it prevents you from doing something is often a clue that what you want to do is not a good idea. Never drop a trigger without consulting the dba. – HLGEM Dec 31 '10 at 18:55

5 Answers5

29

DROP TRIGGER:

Removes one or more triggers from the current database...

You can remove a trigger by dropping it or by dropping the trigger table. When a table is dropped, all associated triggers are also dropped. When a trigger is dropped, information about the trigger is removed from the sysobjects and syscomments system tables.

Use DROP TRIGGER and CREATE TRIGGER to rename a trigger. Use ALTER TRIGGER to change the definition of a trigger...

gnat
  • 6,213
  • 108
  • 53
  • 73
Joe
  • 41,484
  • 20
  • 104
  • 125
  • Works! ;) Heh, thanks! I'll mark is as the correct answer in 11 minutes, StackOverflow prevents me from accepting it immediately. – Teekin Dec 31 '10 at 17:25
  • 3
    Fyi. In sql, delete is a row operation, drop is a db object (for example, a trigger) operation. – DwB Dec 31 '10 at 17:27
  • Yeah, it's really obvious in retrospect. – Teekin Dec 31 '10 at 17:32
  • Remember to add the Schema prefix if it isn't dbo. So if the schema is 'MySchema' write DROP TRIGGER MySchema.SomeTrigger – Loke Apr 12 '18 at 16:12
19

To drop a trigger, this works:

DROP TRIGGER [trigger_name];

If you want to check weather trigger exist before a drop, then use:

SELECT * FROM [sys].[triggers] WHERE [name] = 'MyTrigger'

For more check out http://www.tsql.info/triggers/drop-trigger.php and https://stackoverflow.com/a/636470/2218697

Community
  • 1
  • 1
Shaiju T
  • 6,201
  • 20
  • 104
  • 196
2

For SQL Server 2016 and above

DROP TRIGGER IF EXISTS [dbo].[trg]

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

Lord Darth Vader
  • 1,895
  • 1
  • 17
  • 26
1

I can Drop a Trigger with the Following Query

DROP TRIGGER [Trigger_Name]

          (OR)            

DROP TRIGGER Trigger_Update

Hope this Must helpfull...

Ismayil S
  • 223
  • 3
  • 20
0
DROP TRIGGER IF EXISTS [dbo].[trigger_name];
Colonel_Old
  • 852
  • 9
  • 15