Some time we required to find out the information about triggers i.e. name of all triggers present in current database and their execution order, their type , last modification data then we can use below code snippet.
- SELECT
-
- sys.TABLES.name,
-
- sys.TRIGGERS.name,
-
- sys.TRIGGER_EVENTS.type,
-
- sys.TRIGGER_EVENTS.TYPE_DESC,
-
- IS_FIRST,
-
- IS_LAST,
-
- sys.TRIGGERS.CREATE_DATE,
-
- sys.TRIGGERS.MODIFY_DATE
-
- FROM sys.TRIGGERS
-
- INNER JOIN sys.TRIGGER_EVENTS
-
- ON sys.TRIGGER_EVENTS.object_id = sys.TRIGGERS.object_id
-
- INNER JOIN sys.TABLES
-
- ON sys.TABLES.object_id = sys.TRIGGERS.PARENT_ID
-
- ORDER BY MODIFY_DATE
Above code provide the information about all triggers present in current database if we want to retrieve information about
a particular trigger then we can use below code.
- SELECT
-
- sys.TABLES.name,
-
- sys.TRIGGERS.name,
-
- sys.TRIGGER_EVENTS.type,
-
- sys.TRIGGER_EVENTS.TYPE_DESC,
-
- IS_FIRST,
-
- IS_LAST,
-
- sys.TRIGGERS.CREATE_DATE,
-
- sys.TRIGGERS.MODIFY_DATE
-
- FROM sys.TRIGGERS
-
- INNER JOIN sys.TRIGGER_EVENTS
-
- ON sys.TRIGGER_EVENTS.object_id = sys.TRIGGERS.object_id
-
- INNER JOIN sys.TABLES
-
- ON sys.TABLES.object_id = sys.TRIGGERS.PARENT_ID
-
- WHERE sys.TRIGGERS.name= 'Trigger_Name' /* Trigger Name */
-
- ORDER BY MODIFY_DATE