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.