SQL SERVER – 2005 – Disable Triggers – Drop Triggers

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)

SQL Scripts, SQL Server Security, SQL Trigger
Previous Post
SQL SERVER – Fix : Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns
Next Post
SQL SERVER – Script to Find SQL Server on Network

Related Posts

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?

    Reply
  • Thanks i find the triggers just like sql 2000 thank’s a lot man

    Reply
  • Article is small and nice but explanation of imran is very nice and very useful,truly DDL triggers is very nice feature.

    Reply
  • Very Precise explanation, Thanks .
    It has solved all my confusion about the storage of Triggers in DB.

    Reply
    • 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.

      Reply
  • Abhishek Singh
    January 25, 2012 3:02 pm

    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?

    Reply
  • 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

    Reply
  • 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……..

    Reply

Leave a Reply