Microsoft® SQL Server® 2012 Bible

886

Part VII: Monitoring and Auditing

WHEN 1 THEN 'disabled'

END AS status

FROM sys.triggers Tr

JOIN sys.objects Ob

ON Tr.parent_id = Ob.object_id

JOIN sys.schemas Sc

ON Ob.schema_id = Sc.schema_id

WHERE Tr.type = 'TR' and Tr.parent_class = 1

ORDER BY Sc.name + '.' + Ob.name, Tr.name

Result:

table trigger type status

------------------------------ -------------------- ---------- -------

HumanResources.Employee dEmployee instead of enabled

Person.Person iuPerson after enabled

Production.WorkOrder iWorkOrder after enabled

Production.WorkOrder uWorkOrder after enabled

Purchasing.PurchaseOrderDetail iPurchaseOrderDetail after enabled

Purchasing.PurchaseOrderDetail uPurchaseOrderDetail after enabled

Purchasing.PurchaseOrderHeader uPurchaseOrderHeader after enabled

Purchasing.Vendor dVendor instead of enabled

Sales.SalesOrderDetail iduSalesOrderDetail after enabled

Sales.SalesOrderHeader uSalesOrderHeader after enabled

Triggers and Security

Only the table or view owner, members of the sysadmin fi xed server role, or the dbowner or

ddldmin fi xed database roles have permission to create, alter, drop, enable, or disable triggers.

A trigger executes under the security context of the owner of their parent object unless

modifi ed with the EXECUTE AS clause when the trigger is created or altered.

Working with the Transaction

SQL Server provides several ways for the trigger to determine the effects of the DML state-

ment. The fi rst two methods are the UPDATE() and columns_updated() functions,

which are used to determine which columns were potentially affected by the DML state-

ment. The other methods use deleted and inserted images, which contain the before

and after data sets.

Determining the Updated Columns

SQL Server provides the UPDATE() function to test if a single column is affected by the

DML transaction:

IF UPDATE(ColumnName)

c36.indd 886c36.indd 886 7/31/2012 10:03:08 AM7/31/2012 10:03:08 AM

http://www.it-ebooks.info