#0263 – SQL Server – Interview Question – Script to Identify DML Triggers (Active/Disabled) and Trigger Events


For an entry level DBA position, some of the common interview questions around the subject of database DML triggers are:



  • How many triggers exist in your database?

  • Can you classify them into INSERT, UPDATE and DELETE triggers?

This post presents a script that answers all the questions above. The script below involves querying the Catalog Views – sys.triggers and sys.trigger_events to answer these questions:

USE AdventureWorks2008R2 ;
GO
SELECT st.name AS TriggerName,
OBJECT_NAME(st.parent_id) AS ParentTableName,
st.is_ms_shipped AS IsMSShipped,
st.is_disabled AS IsDisabled,
st.is_not_for_replication AS IsNotForReplication,
st.is_instead_of_trigger AS IsInsteadOfTrigger,
te.type AS TypeId,
te.type_desc AS TypeDescription,
te.is_first AS IsTriggerFiredFirst,
te.is_last AS IsTriggerFiredLast
FROM sys.triggers AS st
INNER JOIN sys.trigger_events AS te ON te.object_id = st.object_id
WHERE st.parent_id > 0
AND st.is_ms_shipped = 0
ORDER BY st.parent_id, te.type ASC;
GO

/* Results:
TriggerName ParentTableName IsMS Is IsNotFor IsInstead Type Type IsTrigger IsTrigger
Shipped Disabled Replication Trigger Id Description FiredFirst FiredLast
——————– ——————– ——- ——– ———– ——— —- ———– ———– ———
dVendor Vendor 0 0 1 1 3 DELETE 0 0
iWorkOrder WorkOrder 0 0 0 0 1 INSERT 0 0
uWorkOrder WorkOrder 0 0 0 0 2 UPDATE 0 0
iPurchaseOrderDetail PurchaseOrderDetail 0 0 0 0 1 INSERT 0 0
uPurchaseOrderDetail PurchaseOrderDetail 0 0 0 0 2 UPDATE 0 0
uPurchaseOrderHeader PurchaseOrderHeader 0 0 0 0 2 UPDATE 0 0
iduSalesOrderDetail SalesOrderDetail 0 0 0 0 1 INSERT 0 0
iduSalesOrderDetail SalesOrderDetail 0 0 0 0 2 UPDATE 0 0
iduSalesOrderDetail SalesOrderDetail 0 0 0 0 3 DELETE 0 0
dEmployee Employee 0 0 1 1 3 DELETE 0 0
uSalesOrderHeader SalesOrderHeader 0 0 1 0 2 UPDATE 0 0
iuPerson Person 0 0 1 0 1 INSERT 0 0
iuPerson Person 0 0 1 0 2 UPDATE 0 0
*/


Here are some of the ways in which the query given above can be helpful in addition to answering interview questions:



  • Detemine if a trigger exists on a database

  • The IsDisabled flag shows whether a trigger is active or not

  • Is the trigger an INSTEAD OF trigger? 


Further Reading:



  • sys.triggers: [Link]

  • sys.trigger_events: [Link]

  • Catalog views and DMVs – An introduction: [Link]

  • SQL Server – 2012 – What’s new? – Dynamic Management Views (DMV) and Catalog View changes: [Link]

Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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