Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to Enable or Disable All the Triggers on a Table and Database?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 429
    Comment on it

    Sometimes we need to disable trigger on a table especially when performing admin tasks on a table. Following commands provide a quick way to disable all the triggers for a table. Please note

    that when we are diabling triggers on a table we will have to specify the name of the table. Howevere in case we are enabling/disabling on database or server , we just have to specify word

    like database and all server.Also we need to keep the current context of the database where you want to disable database.

    For enabling on a Table

    ENABLE TRIGGER safety ON TableName;
    GO
    

    For enabling on a Database

    ENABLE TRIGGER ALL  safety ON DATABASE;
    GO
    

    For enabling on a Server

    ENABLE TRIGGER safety ON ALL SERVER;
    GO
    

    For disabling on a Table

    DISABLE TRIGGER safety ON TableName;
    GO
    

    For disabling on a Database

    DISABLE TRIGGER ALL safety ON DATABASE;
    GO
    

    For disabling on a Server

    DISABLE TRIGGER safety ON ALL SERVER;
    GO
    

    However, if you want to disable the trigger only for a particular statement there is no default mechanism to do this. Following is one custom approach using a temp table:

    In this approach we create a temporary table before we execute the statement that would fire the trigger. Now the trigger will check for the existence of the temporary table and if it exists

    the trigger will return and not execute the code. If the temporary table doe not exist trigger will execute its code as normal.

    IF OBJECT_ID('dbo.Table1') IS NOT NULL 
    DROP TABLE dbo.Table1 
    GO 
    CREATE TABLE dbo.Table1(ID INT) 
    GO 
    
    -- Creating a trigger 
    CREATE TRIGGER TRTest ON dbo.Table1 
    FOR INSERT,UPDATE,DELETE 
    AS 
    IF OBJECT_ID('tempdb..#Skip') IS NOT NULL RETURN 
    
    --Remaninig Code
    GO
    

    In case we do not want the trigger to fire for a statement,we simply need to create a temporary table in the statement as below.

    CREATE TABLE #Skip(ID INT) 
    -- Actual statement 
    INSERT dbo.Table1 VALUES(10) 
    DROP TABLE #Skip
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: