How to check if a trigger fires on INSERT, UPDATE or DELETE statements?

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.

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

StackExchange Database Administrators Q#250780, answer score: 12