SQL to list all triggers

Code Solution

SELECT
  trigger_name,
  action_timing,
  event_manipulation,
  action_statement,
  action_orientation,
  action_condition,
  action_order
FROM information_schema.triggers
WHERE
  event_object_table = 'table_name';

Explanation

The provided SQL query retrieves a detailed list of all triggers associated with a specific table, identified by table_name. By querying the information_schema.triggers system table, we can access comprehensive information about each trigger.

Implementation

To implement this code effectively, follow these steps:

  1. Replace table_name with the actual name of the table for which you want to list triggers.
  2. Execute the query in your database environment using a tool like a database client or query editor.
  3. The query results will display a table with the following columns:
    • trigger_name: Name of the trigger
    • action_timing: Timing of the trigger’s activation (e.g., BEFORE, AFTER)
    • event_manipulation: Type of database operation that triggers the trigger (e.g., INSERT, UPDATE, DELETE)
    • action_statement: SQL statement executed when the trigger is activated
    • action_orientation: Whether the action statement applies to each row (ROW) or the entire table (STATEMENT)
    • action_condition: Conditional expression that determines when the action statement is executed
    • action_order: Order in which multiple triggers are executed when multiple triggers are defined on the same event

Additional Notes

  • This query provides detailed information about triggers, but it does not include the trigger definition or the source code of the action statement.
  • Triggers can be defined on views as well as tables. To list triggers on a view, specify the view name as event_object_table.
  • To gain a deeper understanding of triggers and their usage, refer to the documentation provided by your database vendor.