Tips for Using Triggers in SQL Server Database

Triggers are most important part of the SQL Server Database. Triggers are the special stored procedures which are used to perform actions on the database. These actions have different categories like: DDL, DML and logon etc.

Tip 1: Find the list of triggers using sp_MSforeachtable

If you want to see the list of the triggers along with the schema then, you can use the sp_MSforeachtable to get it. Use this stored procedure like this:

USE AdventureWorks2012
GO
EXEC sp_MSforeachtable
@command1 = 'sp_helptrigger ''?''' ,@whereand = 'And Object_id In (Select parent_id From sys.triggers)';

After executing it, you will get the list of the triggers as the output. See the below image which shows the out of the above query:

In the above output, you can see the different columns with the details like: trigger name, owner, operations and schema.  

  

Tip 2: Find the list of enabled and disabled triggers

Every database has tables and if the user creates any trigger then it comes up under table section. A User has options to enable and disable triggers by clicking on it or by the query. Just right click on the trigger which you want to enable or disable it and it will look like this:

To enabled or disabled the trigger using query, just execute the below queries.

Disable the trigger

USE Database_name

GO

DISABLE TRIGGER Trigger_Name ON Table_Name

GO

Enable the trigger:

USE Database_name

GO

ENABLE TRIGGER Trigger_Name ON Table_Name

GO

To see the status of all the triggers, simply run the following query:

Select * from sys.triggers

In this case, the user will get the status of all triggers presented in the database.

In the above image, the red rectangular box showing the status of the trigger. In this section, the output always presents in the binary form (0 or 1). In the above image the trigger “Trg_InsertSalePerson” is disabled because its binary value is 1 in the “is_disable” column. To check its result, now enable the same trigger and you will get this message on the screen:

Now run the same query again

Select * from sys.triggers

At this time, the output will show zeros for both the triggers:

We have one more option to see the status of the triggers. Execute this query 

Select TBL.name AS TableName,

Schema_name(TBL.schema.id) AS Table_SchemaName,

TRG.name AS TriggerName,

TRG.parent_class_desc,

CASE

WHEN TRG.is_disabled = 0 THEN ‘Enable’

ELSE ‘Disable’

END AS TRG_Status 

From sys.triggers TRG

INNER JOIN sys.tables TBL

ON TBL.OBJECT_ID = TRG.parent_id

AND is_disabled = 0

The output will be:

Now, the user can see the difference TRG_Status and both are enabled. So these are the simple tips about to find the status of triggers.