There are two ways to prevent trigger from firing.
1) Drop Trigger
Example:
DROP TRIGGER TriggerName
GO
2) Disable Trigger
DML trigger can be disabled two ways. Using ALETER TABLE statement or use DISABLE TRIGGER. I prefer DISABLE TRIGGER statement.
Syntax:
DISABLE TRIGGER { [ schema . ] trigger_name
[ ,...n ] | ALL }
ON { OBJECT_NAME | DATABASE | ALL SERVER } [ ; ]
Example:
DISABLE TRIGGER TriggerName ON TableName
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
9 Comments. Leave new
Hi Pinal,
the article is very useful. however, how do I alter an existing trigger? I am able to find the trigger using SELECT *
FROM sys.triggers but I can’t view the content / alter it
I’ve seen in SQL Server 2000 you can right click the table which has the triggers and All Tasks > Manage Triggers but can’t do this in SQL 2005.
I don’t see any triggers listed in the db >programmability> database triggers section
Do I have to delete the entire trigger and re-create it? How do I view the content of an existing trigger using an SQL statement?
Thanks i find the triggers just like sql 2000 thank’s a lot man
Article is small and nice but explanation of imran is very nice and very useful,truly DDL triggers is very nice feature.
Very Precise explanation, Thanks .
It has solved all my confusion about the storage of Triggers in DB.
To add one more point for triggers, there is also something called server level triggers which can be found under server node->Server Objects->Triggers. This is applicable to all the databases under that server like DDL_LOGIN_EVENTS etc to track logins.
hello sir,
i am using sql server 2008 and i have got 5 tables in my database.
i want to make sure that 3 of the 5 tables could not be dropped by any user
(even the admin) and the other two can be dropped.
the 3 tables (not to be dropped) does not have any referential integrity.
does this problem have any other solution than schemabinding, i.e. using triggers?
yes !! of course..
as u are telling that there are 5 tables.. for 3 tables that u don’t want to drop u can write triggers as normal but include a statement like if drop/delete command raises ‘rollback trans’ end..
Njoy..
note: its just my thought i hve not tried. excuse me if it is wrong
How to drop a trigger for DDL events?
I want to create and alter a table in database but error is coming saying, ‘Cannot drop the trigger ‘trigger_altertable’, because it does not exist or you do not have permission’.
If i want to alter a table. Can u plz help me out of this……..