11

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.

12

The sys.triggers table does not expose this data, instead we have two options:


OBJECTPROPERTY

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.


sys.trigger_events

As per the docs this contains a row per event for which a trigger fires. Specifically the columns we're interested in are type and type_desc.

type is a foreign key to the sys.trigger_event_types table - type_desc seems to contain the type_name column from the sys.trigger_event_types table.

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.