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.