SELECT table_name = OBJECT_NAME(parent_object_id) ,
trigger_name = name ,
trigger_owner = USER_NAME(schema_id) ,
OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
WHEN 1 THEN 'Disabled'
END AS status
WHERE type = 'TR'
ORDER BY OBJECT_NAME(parent_object_id)
This works great – very handy to have when you need to see what triggers are on what tables in your database!
PS – I used the
tag in the HTML view to ‘force’ the single quotes to behave correctly in the code snippet above. Thank you to HTML Goodies
for a great article on this!
PSS – I should also add that I great information with these 2 articles regarding triggers – UBER-helpful!!
I have read many posts on Pinal’s blog, http://blog.sqlauthority.com/, over the last 2 years and just wanted to write a quick post giving him a shout-out. I really like the step-by-step way that he describes some SQL-related process. And just recently, the comparison of developers to Iron Man and Chhota Bheem are fun articles that I particularly enjoyed. Thanks Pinal – keep posting awesome-ness!
On a side note, I have not posted anything since the beginning of May (or maybe the end of April!). I do plan on writing more and writing regularly – it was kind of a crazy month in May and then June has been more like ‘finding my sea-legs’ at my new work. I’ve transitioned careers and am now working as a database developer and programmer for a small software company.