How to check if a trigger fires on INSERT, UPDATE or DELETE statements?
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';