#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:

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
*/

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 )

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