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