Can I select all the tables with more than one update trigger

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_i开发者_运维技巧d
FROM sys.triggers
GROUP BY parent_id
HAVING COUNT(*)>1)

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;

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)