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 (http://blog.SQLAuthority.com)

12 thoughts on “SQL SERVER – 2005 – Disable Triggers – Drop Triggers

  1. Pingback: SQL SERVER - Disable All Triggers on a Database - Disable All Triggers on All Servers Journey to SQL Authority with Pinal Dave

  2. 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?

    Like

  3. Dear Joyce,

    This feature is also available in SQL Server 2005.

    If you login into SSMS, expand Servername- expand database – expand table- under tables 4th subobject you will see trigger – expand triggers ( to see all triggers in that table) – right click on any one of the trigger and click modify and you will see a new query window with SQL code for that trigger, If you want to make changes to this trigger, make necessary changes in the sql code and then hot execute button at the top. ( same as to execute any query.) and you are done.

    You also mentioned something about, Programmability under Database ( in object explorer, SSMS), let me explain you in brief what exactly is this.

    Objects in a database can be : Tables, views, stored procedures and functions ….

    So when we are talking about triggers, these are specific for tables, because triggers (DML, Data Manipulation Language Triggers) can be created on tables for/instead of insert ,update and delete. That is why you will see triggers under tables but not under Programmability.

    Now the second question, When you expand programmability, you will see database triggers , what are these triggers ???

    This is a brand new concept in SQL Server 2005, these are called DDL Triggers, Data Definition Language ( these are not available in SQL Server 2000).

    These triggers, as the name defines are used for all DDL operations like create, alter, drop… You cant imagine how many types of these triggers are available in SQL Server 2005. DML like I said are only 3 types, Insert update and delete, but if you read books online, you will see, there are many many types for DDL Triggers. This is very good feature in SQL Server 2005. Since these triggers are not for a specific table, they cannot be placed under tables( in SSMS, object Explorer) , so I assume designer found it very handy to keep these triggers under programmability, and names these triggers as Database triggers, since these triggers are at database level but not for a specific table.

    Here is a very quick example for both of these triggers,

    — Create two tables
    create table example1 ( eid1 int )
    create table example2 ( eid2 int )

    — create insert trigger on table : example1
    create trigger Tr_ex1
    on example1
    for insert
    as
    begin
    insert into example2 select * from inserted
    end

    — insert into table: example1
    insert into example1 values ( 1)

    — check table: example2 if this value is inserted
    select * from example2

    By this time you should be able to see this trigger ( Tr_ex1) under table name example1. ( in SSMS , object explorer). Right click on trigger and click modify, you will see the same SQL code as above, if you want to modify, you can modify and click execute and you are done.

    — create another table to store information about, who dropped tables, when and from which database and at what time.
    create table example3 ( UserName varchar (max), databasename varchar(max), Date_time datetime)

    — create DDL trigger
    CREATE TRIGGER Tr_ex2
    ON DATABASE
    FOR DROP_TABLE
    AS
    insert into example3 select suser_name() , db_name(), getdate()

    By this time you should be able to see a database trigger, under programmability by name Tr_ex2. ( in SSMS , object explorer). Right click on trigger and click modify, you will see the same SQL code as above, if you want to modify, you can modify and click execute and you are done.

    — Drop table: example2
    drop table example2

    — check table:example3 if it shows information about who dropped table from which database and at what time.

    select * from example3

    Result:

    IMRAN\imran databasename 2008-08-23 23:10:09.617

    Read more about DDL Triggers : http://blog.sqlauthority.com/2007/07/24/sql-server-2005-server-and-database-level-ddl-triggers-examples-and-explanation/

    Hope this helps,
    Imran.

    Like

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

      Like

  4. Pingback: SQL SERVER – Significance of Various Kinds of Triggers- Quiz – Puzzle – 2 of 31 « SQL Server Journey with SQL Authority

  5. 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?

    Like

  6. 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

    Like

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

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s