SQL:Disable Or Enable a Trigger

Triggers are enabled by default when they are created. Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger does not fire when any Transact-SQL statements on which it was programmed are executed. Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.


Purpose of the following Trigger
---------------------------------
--AVOIDE TO DROP TABLES FROM DATABASE

USE Test_Manoj
Go

CREATE TRIGGER TRG_DB_SAFTY
ON DATABASE
FOR DROP_TABLE
AS
RAISERROR('DROP CANNOT POSSIBLE,PLEASE CONTACT ADMIN..',16,1)
ROLLBACK ;

After creation of trigger any point of time a trigger can be disable or enable by the user.For Disable a trigger(de-activate) DISABLE keyword is used as follows:-

--Disable Trigger on Database

USE Test_Manoj
Go
disable TRIGGER TRG_DB_SAFTY on database

For Enable a trigger on Database,any other object we need ENABLE keyword as follows:-


--Enable trigger on Database

USE Test_Manoj
Go
ENABLE TRIGGER TRG_DB_SAFTY on database

Disabling all triggers

----------------------------
--Disable all triggers on all server

USE Test_Manoj
Go
DISABLE Trigger ALL ON ALL SERVER

OR

--Disable all triggers in Database

USE Test_Manoj
Go
DISABLE Trigger ALL ON DATABASE
Go


Disable Trigger on Table
---------------------------------
USE Test_Manoj
Go
DISABLE Trigger ALL ON DBO.MY_EMP
Go





Posted By:-javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com