I stumbled across a scenario where I had to find all the triggers associated with few tables. Found the below queries and felt that they are worth sharing. I am listing the helpful sql queries!
To get a list of all the triggers in the database
select * from sysobjects where type = "TR"
To see all the objects related to a trigger
sp_depends <trigger_name>
To get the list of triggers based on a table name
select name
from sysobjects
where xtype='TR'
and id in (select id from syscomments where text like '%MY-TABLE-NAME%')
To find the triggers for a list of tables(this is a good one)
SELECT
T.name AS TableName
,O.name TriggerName
FROM sysobjects O
INNER JOIN sys.tables T ON T.object_id = O.parent_obj
WHERE O.type = 'TR' AND T.name IN ('<table1>','<table2>',....)
ORDER BY TableName
During this time, I was doing some monkey testing and found out that its not possible to create a trigger under a schema different from the table schema (just a hope to know an additonal thing while I am there :-))
Important triggers tip : Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if wealways just update single rows, coding this way will prevent issues if there is a multiple row change.More here
Read More..
[16 clicks]
Published under:
SQL Server Tips · · · ·