find all trigger associated with SQL Table

Need to list all triggers in SQL Server database with table name and table's schema

How To Find List All Triggers in SQL Server?

The below T-SQL Query will help us to get the results:

Example 1:

To list all triggers in a SQL Server, you query data from the sys.triggers view: 

SELECT    name,    is_instead_of_trigger

    FROM     sys.triggers 

WHERE      type = 'TR';


Example 2:

select as trigger_name,

    schema_name(tab.schema_id) + '.' + as [table],


                              when is_instead_of_trigger = 1

                                             then 'Instead of'

        else 'After'

               end as [activation],


                              when objectproperty(trg.object_id, 'ExecIsUpdateTrigger') = 1

                                             then 'Update '

                               else ''


    + case

                              when objectproperty(trg.object_id, 'ExecIsDeleteTrigger') = 1

                                              then 'Delete '

                              else ''


    + case

                              when objectproperty(trg.object_id, 'ExecIsInsertTrigger') = 1

                                              then 'Insert'

                              else ''


    ) as [event],


                              when trg.parent_class = 1

                                             then 'Table trigger'

        when trg.parent_class = 0

                                             then 'Database trigger'

    end [class],

    case when trg.[type] = 'TA'

                                             then 'Assembly (CLR) trigger'

        when trg.[type] = 'TR'

                                             then 'SQL trigger'

        else ''

               end as [type],


                              when is_disabled = 1

                                             then 'Disabled'

        else 'Active'

               end as [status],

    object_definition(trg.object_id) as [definition]

from sys.triggers trg

    left join sys.objects tab

        on trg.parent_id = tab.object_id

order by




Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog -
My Books - Book 1 and Book 2 Powered by Blogger.