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
whenis_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
whentrg.parent_class =1
then'Table trigger'
whentrg.parent_class =0
then'Database trigger'
end [class],
case when trg.[type] = 'TA'
then'Assembly (CLR) trigger'
whentrg.[type] = 'TR'
then'SQL trigger'
else ''
end as [type],
case
whenis_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
