MySQL - Show Trigger

MySQL - Listing Triggers

Triggers are a special type of stored program in MySQL, automatically executed or ‘fired’ in response to certain events on a particular table. These events are INSERT, UPDATE, or DELETE operations. As your database schema grows, it becomes essential to have a way to list and inspect the triggers you’ve created.

MySQL provides two primary ways to see existing triggers: the SHOW TRIGGERS command and by querying the INFORMATION_SCHEMA.TRIGGERS table. Understanding both methods will give you greater flexibility in managing your database.

Method 1: Using the SHOW TRIGGERS Statement

The SHOW TRIGGERS statement is a simple, direct command to display information about triggers defined in a database.

Syntax

SHOW TRIGGERS [{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

Example

First, let’s create a table and a couple of triggers to work with. We’ll create an AuditLog table to record changes to an Employees table.

CREATE TABLE Employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

CREATE TABLE AuditLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(20),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger for logging salary updates
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO AuditLog(employee_id, action, old_salary, new_salary)
        VALUES(OLD.id, 'SALARY_UPDATE', OLD.salary, NEW.salary);
    END IF;
END;//
DELIMITER ;

-- Trigger for logging new hires
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog(employee_id, action, new_salary)
    VALUES(NEW.id, 'NEW_HIRE', NEW.salary);
END;//
DELIMITER ;

Now, to see the triggers we’ve just created in the current database, use the SHOW TRIGGERS command. The \G delimiter formats the output vertically for better readability.

SHOW TRIGGERS\G

Output Explained

The output will list each trigger with detailed information:

*************************** 1. row ***************************
             Trigger: after_employee_insert
               Event: INSERT
               Table: Employees
           Statement: BEGIN
    INSERT INTO AuditLog(employee_id, action, new_salary)
    VALUES(NEW.id, 'NEW_HIRE', NEW.salary);
END
              Timing: AFTER
             Created: ...
            sql_mode: ...
             Definer: 'user'@'host'
character_set_client: ...
collation_connection: ...
  Database Collation: ...
*************************** 2. row ***************************
             Trigger: before_employee_update
               Event: UPDATE
               Table: Employees
           Statement: BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO AuditLog(employee_id, action, old_salary, new_salary)
        VALUES(OLD.id, 'SALARY_UPDATE', OLD.salary, NEW.salary);
    END IF;
END
              Timing: BEFORE
             Created: ...
            sql_mode: ...
             Definer: 'user'@'host'
character_set_client: ...
collation_connection: ...
  Database Collation: ...
2 rows in set (0.00 sec)

Filtering SHOW TRIGGERS Output

You can filter the results using FROM/IN for a specific database, or LIKE/WHERE for more complex conditions.

Using FROM or IN

To show triggers from a specific database (e.g., company_db) regardless of which database you are currently using:

SHOW TRIGGERS FROM company_db;

Using LIKE or WHERE

The LIKE clause filters by trigger name, while the WHERE clause allows for more complex conditions on any output column. WHERE is generally more flexible.

-- Using LIKE to find triggers with 'before' in their name
SHOW TRIGGERS LIKE '%before%';

-- Using WHERE to find all triggers on the 'Employees' table
SHOW TRIGGERS WHERE `Table` = 'Employees';

-- Using WHERE to find all triggers that fire on UPDATE events
SHOW TRIGGERS WHERE Event = 'UPDATE'\G

Method 2: Querying the INFORMATION_SCHEMA

The INFORMATION_SCHEMA is a set of read-only tables providing metadata about the database itself. Querying the TRIGGERS table within it is the standard SQL way to get information about triggers and is often more flexible for scripting and reporting.

Example Query

This query retrieves key information about triggers in a specific database, similar to SHOW TRIGGERS.

SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION AS `Event`,
    EVENT_OBJECT_TABLE AS `Table`,
    ACTION_TIMING AS `Timing`,
    ACTION_STATEMENT AS `Statement`
FROM
    INFORMATION_SCHEMA.TRIGGERS
WHERE
    TRIGGER_SCHEMA = 'your_database_name';

This approach allows you to use the full power of SELECT, including joins, complex filtering, and ordering, which is not possible with SHOW TRIGGERS.

Comparing SHOW TRIGGERS and INFORMATION_SCHEMA

Aspect	SHOW TRIGGERS	INFORMATION_SCHEMA.TRIGGERS
Type	MySQL-specific command.	Standard SQL approach, more portable.
Flexibility	Limited filtering with LIKE and WHERE.	Full SELECT capabilities (joins, subqueries, ordering, etc.).
Use Case	Quick, interactive inspection of triggers.	Automated scripts, reporting, complex metadata queries.
Permissions	Requires the TRIGGER privilege.	Accessible to users with appropriate access to INFORMATION_SCHEMA.

Listing Triggers Using Modern Client Applications

Here’s how you can programmatically list triggers, using the more flexible INFORMATION_SCHEMA approach.

Node.js (mysql2)
Python (mysql-connector-python)
Java (JDBC)
PHP (mysqli)


Using `mysql2/promise` in Node.js, we can fetch and display trigger details.

```javascript
// Requires: npm install mysql2
const mysql = require('mysql2/promise');

async function listTriggers(dbName) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: dbName
    });

    const sql = `
      SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
      FROM INFORMATION_SCHEMA.TRIGGERS
      WHERE TRIGGER_SCHEMA = ?
    `;

    const [rows] = await connection.execute(sql, [dbName]);

    if (rows.length === 0) {
        console.log(`No triggers found in database '${dbName}'.`);
        return;
    }

    console.log(`Triggers in database '${dbName}':`);
    rows.forEach(trigger => {
        console.log(
          `- ${trigger.TRIGGER_NAME} (${trigger.ACTION_TIMING} ${trigger.EVENT_MANIPULATION} on ${trigger.EVENT_OBJECT_TABLE})`
        );
    });

  } catch (error) {
    console.error(`An error occurred: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

listTriggers('your_database_name');

In Python, you can fetch the results as a list of dictionaries for easy processing.

# Requires: pip install mysql-connector-python
import mysql.connector

def list_triggers(db_name):
    config = {
        'user': 'root',
        'password': 'your_password',
        'host': 'localhost',
        'database': db_name
    }
    sql = """
        SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
        FROM INFORMATION_SCHEMA.TRIGGERS
        WHERE TRIGGER_SCHEMA = %s
    """
    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql, (db_name,))
                triggers = cursor.fetchall()

                if not triggers:
                    print(f"No triggers found in database '{db_name}'.")
                    return

                print(f"Triggers in database '{db_name}':")
                for trigger in triggers:
                    print(f"- {trigger['TRIGGER_NAME']} ({trigger['ACTION_TIMING']} {trigger['EVENT_MANIPULATION']} on {trigger['EVENT_OBJECT_TABLE']})")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")

list_triggers('your_database_name')

Java’s JDBC can be used to query the INFORMATION_SCHEMA and print the results.

import java.sql.*;

public class ListTriggersExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String dbName = "your_database_name";
        String sql = "SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING " +
                       "FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL + dbName, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, dbName);
            ResultSet rs = pstmt.executeQuery();

            System.out.println("Triggers in database '" + dbName + "':");
            boolean found = false;
            while (rs.next()) {
                found = true;
                System.out.printf("- %s (%s %s on %s)%n",
                    rs.getString("TRIGGER_NAME"),
                    rs.getString("ACTION_TIMING"),
                    rs.getString("EVENT_MANIPULATION"),
                    rs.getString("EVENT_OBJECT_TABLE"));
            }
            if (!found) {
                System.out.println("No triggers found.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

In PHP, using prepared statements with mysqli prevents SQL injection and is the recommended approach.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
$dbname = 'your_database_name';

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING ".
       "FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = ?";

$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $dbname);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    echo "Triggers in database '$dbname':\n";
    while($row = $result->fetch_assoc()) {
        printf("- %s (%s %s on %s)\n",
            $row['TRIGGER_NAME'],
            $row['ACTION_TIMING'],
            $row['EVENT_MANIPULATION'],
            $row['EVENT_OBJECT_TABLE']);
    }
} else {
    echo "No triggers found in database '$dbname'.";
}

$stmt->close();
$mysqli->close();
?>