How do I determine whether a trigger is targeting INSERT, UPDATE or DELETE statements?
I've checked in
sys.triggers and I can't see anything related to what operation the trigger fires on.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
sys.triggers table does not expose this data, instead we have two options:
Using the OBJECTPROPERTY metadata function we can return the action as a property as below:
OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger')
This will return a simple bit value indicating whether the trigger fires on that particular action.
As per the docs this contains a row per event for which a trigger fires. Specifically the columns we're interested in are
type is a foreign key to the
sys.trigger_event_types table -
type_desc seems to contain the
type_name column from the
This data can be returned using the following script:
SELECT [o].[name] ,[t].[type_desc] FROM [sys].[trigger_events] AS [t] INNER JOIN [sys].[objects] AS [o] ON [o].[object_id] = [t].[object_id] WHERE [o].[name] = 'TriggerNameHere';