This script will find all the triggers in a Microsoft SQL database, its come in handy when I’ve been trying to work out where in the system people have tied there code into. I’m not a huge fan of triggers as they tend to make things behave unpredictably, its even worse if you don’t know there is a trigger or where it is.

SELECT S2.[name] TableName, S1.[name] TriggerName, 
CASE 
WHEN S2.deltrig = s1.id  THEN 'Delete' 
WHEN S2.instrig = s1.id THEN 'Insert' 
WHEN S2.updtrig = s1.id THEN 'Update' 
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

Found this little script on this site.