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 trg.name as trigger_name,

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

    case

                              when is_instead_of_trigger = 1

                                             then 'Instead of'

        else 'After'

               end as [activation],

    (case

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

                                             then 'Update '

                               else ''

                 end

    + case

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

                                              then 'Delete '

                              else ''

                 end

    + case

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

                                              then 'Insert'

                              else ''

                  end

    ) as [event],

    case

                              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],

    case

                              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 trg.name

 

 

ANIL SINGH

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 - https://www.code-sample.com/
My Books - Book 1 and Book 2

find all trigger associated with SQL Table find all trigger associated with  SQL Table Reviewed by Anil Singh on 5:21 AM Rating: (5)
www.code-sample.com/. Powered by Blogger.
^