views:

35

answers:

2

Can I select all the tables with more than one update trigger? The following code selects all the tables with more than one trigger, but I need only the tables with more than one update trigger. This query also returns tables with one insert trigger and one update one, which is not what I want.

SELECT * FROM sys.triggers
WHERE parent_id IN(SELECT parent_id
FROM sys.triggers
GROUP BY parent_id
HAVING COUNT(*)>1)
A: 

Not very reliable, but it should work if you need something quick and dirty.

SELECT * FROM sys.triggers
WHERE parent_id IN
(SELECT parent_id
FROM sys.triggers tt
JOIN sys.syscomments sc on sc.id=tt.object_id
WHERE sc.text LIKE '%AFTER UPDATE%'
GROUP BY parent_id
HAVING COUNT(*)>1)
Sparky
This is not very efficient, relies on "AFTER UPDATE" being used (you could also say "FOR UPDATE"), and using syscomments also introduces the risk that "AFTER UPDATE" or "FOR UPDATE" would straddle the 8000 character mark and therefore not really appear (though this is unlikely since it appears very close to the beginning of the object definition). This information is otherwise available in the metadata / catalog views, allowing you to determine these things without resorting to brute force string parsing.
Aaron Bertrand
PS to avoid issues with sys.syscomments (which is only provided for backward compatibility and will someday be deprecated), I recommend becoming familiar with sys.sql_modules (which has a definition column that is not limited to 8KB) and the function OBJECT_DEFINITION.
Aaron Bertrand
I agree, this is quick and dirty if you need something for one-time use. I like your solution better...
Sparky
+3  A: 

You should be able to tell this from sys.trigger_events, e.g:

SELECT *
  FROM sys.trigger_events AS te
  INNER JOIN sys.triggers AS t
  ON t.[object_id] = te.[object_id]
  WHERE te.[type] IN (1,2,3);

So to get tables with more than one update trigger:

SELECT OBJECT_NAME(parent_id)
  FROM sys.triggers AS t
  INNER JOIN sys.trigger_events AS te
  ON t.[object_id] = te.[object_id]
  WHERE te.type_desc = 'UPDATE'
  GROUP BY OBJECT_NAME(parent_id)
  HAVING COUNT(*) > 1;
Aaron Bertrand
This works. Thanks Aaron!
SQL Cowboy