SHOW TRIGGERS

MySQL proporciona varias formas de listar y obtener información sobre los triggers definidos en una base de datos.

SHOW TRIGGERS

-- Listar todos los triggers de la base de datos actual
SHOW TRIGGERS;

-- Filtrar por tabla
SHOW TRIGGERS WHERE `Table` = 'productos';

Columnas del resultado

Columna	Descripción
Trigger	Nombre del trigger
Event	INSERT, UPDATE o DELETE
Table	Tabla asociada
Statement	Cuerpo del trigger
Timing	BEFORE o AFTER
Created	Fecha de creación
sql_mode	Modo SQL cuando se creó
Definer	Usuario que lo creó

SHOW CREATE TRIGGER

Muestra la sentencia CREATE completa:

SHOW CREATE TRIGGER tr_productos_before_insert\G

Consultar information_schema

La tabla information_schema.TRIGGERS contiene información detallada:

SELECT
    TRIGGER_NAME AS nombre,
    EVENT_MANIPULATION AS evento,
    EVENT_OBJECT_TABLE AS tabla,
    ACTION_TIMING AS momento,
    ACTION_ORDER AS orden,
    CREATED AS creado
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tienda_mysql'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;

Triggers por tabla

SELECT
    EVENT_OBJECT_TABLE AS tabla,
    COUNT(*) AS total_triggers
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tienda_mysql'
GROUP BY EVENT_OBJECT_TABLE
ORDER BY total_triggers DESC;

Ver el cuerpo del trigger

SELECT TRIGGER_NAME, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tienda_mysql'
AND TRIGGER_NAME = 'tr_productos_before_insert';

Buscar triggers por evento

-- Todos los triggers BEFORE INSERT
SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tienda_mysql'
AND ACTION_TIMING = 'BEFORE'
AND EVENT_MANIPULATION = 'INSERT';

-- Todos los triggers AFTER en tabla pedidos
SELECT TRIGGER_NAME, EVENT_MANIPULATION
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tienda_mysql'
AND EVENT_OBJECT_TABLE = 'pedidos'
AND ACTION_TIMING = 'AFTER';

Verificar si un trigger existe

SELECT COUNT(*) AS existe
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tienda_mysql'
AND TRIGGER_NAME = 'tr_productos_before_insert';

En el siguiente artículo veremos cómo eliminar triggers con DROP TRIGGER.

Escrito por Eduardo Lázaro