#0277 – SQL Server – Interview Question – Script to Identify DML Triggers and Trigger types


As a DBA, it is very important to be aware about the database schema that you own. For an entry level DBA position, some of the common interview questions around the subject of maintaining database DML triggers are:



  • How many DML triggers exist in your database?

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

  • Given any Microsoft SQL Server database, can you answer both these questions?

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:

[code lang=text]
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
*/
[/code]

Further Reading



  • sys.triggers [Link]

  • sys.trigger_events [Link]

Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

One thought on “#0277 – SQL Server – Interview Question – Script to Identify DML Triggers and Trigger types

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.