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.