Procedimientos y triggers

Hasta ahora toda la lógica (validar stock, calcular totales) la pondría tu aplicación. Pero las bases de datos también pueden ejecutar código propio: procedimientos almacenados, funciones y triggers que se disparan solos cuando algo cambia. En esta lección verás una introducción práctica en MySQL y PostgreSQL, con casos reales como auditoría y actualización automática de stock, y cuándo conviene (y cuándo no) usarlos.

Concepto

Objeto	Qué es	Se ejecuta	Analogía
Función	Código que recibe parámetros y devuelve un valor	Dentro de una consulta: `SELECT f(x)`	Una fórmula de Excel personalizada
Procedimiento	Código que realiza una tarea (puede modificar datos)	Con `CALL nombre(...)`	Una macro
Trigger (disparador)	Código que se ejecuta automáticamente ante un `INSERT`, `UPDATE` o `DELETE`	Solo, sin que nadie lo llame	Un sensor de movimiento que enciende la luz

La sintaxis cambia bastante entre motores, así que verás ambas versiones lado a lado. Para profundizar en PostgreSQL tienes Funciones y PL/pgSQL.

Funciones

Ejemplo: precio con IVA

MySQLPostgreSQL

sql

DELIMITER //

CREATE FUNCTION precio_con_iva(precio DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(precio * 1.16, 2);
END //

DELIMITER ;

sql

CREATE OR REPLACE FUNCTION precio_con_iva(precio NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT ROUND(precio * 1.16, 2);
$$;

Se usa como cualquier función integrada:

sql

SELECT nombre, precio, precio_con_iva(precio) AS con_iva
FROM productos
WHERE categoria_id = 2;

nombre	precio	con_iva
Libro Aprende SQL	349.00	404.84
Cien años de soledad	289.00	335.24

¿Qué es DELIMITER?

En el cliente de MySQL, el ; indica "fin de la instrucción, ejecútala". Pero dentro del cuerpo de una función hay varios ;. DELIMITER // cambia temporalmente el terminador a // para que el cliente envíe todo el bloque junto; al final se regresa con DELIMITER ;. En phpMyAdmin hay un campo "Delimitador" para lo mismo. En PostgreSQL el cuerpo va entre $$ ... $$ y no hace falta.

Ejemplo: total de un pedido

MySQLPostgreSQL

sql

DELIMITER //

CREATE FUNCTION total_pedido(p_pedido_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
  DECLARE v_total DECIMAL(10,2);

  SELECT COALESCE(SUM(cantidad * precio_unitario), 0)
    INTO v_total
  FROM detalle_pedido
  WHERE pedido_id = p_pedido_id;

  RETURN v_total;
END //

DELIMITER ;

sql

CREATE OR REPLACE FUNCTION total_pedido(p_pedido_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(cantidad * precio_unitario), 0)
    INTO v_total
  FROM detalle_pedido
  WHERE pedido_id = p_pedido_id;

  RETURN v_total;
END;
$$;

sql

SELECT id, estado, total_pedido(id) AS total FROM pedidos ORDER BY id;

id	estado	total
1	entregado	1597.00
2	entregado	1847.00
3	enviado	3499.00
4	pendiente	1146.00
5	cancelado	899.00

Rendimiento

Una función que hace un SELECT por dentro, usada sobre 100,000 filas, ejecuta 100,000 consultas. Para listados grandes, un JOIN + GROUP BY es mucho más rápido.

Procedimientos almacenados

Un procedimiento agrupa varias operaciones. Ejemplo real: cancelar un pedido devolviendo el stock, todo en una transacción.

MySQLPostgreSQL

sql

DELIMITER //

CREATE PROCEDURE cancelar_pedido(IN p_pedido_id INT)
BEGIN
  DECLARE v_estado VARCHAR(20);

  -- Si ocurre cualquier error SQL, deshacer y relanzarlo
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  SELECT estado INTO v_estado
  FROM pedidos WHERE id = p_pedido_id
  FOR UPDATE;

  IF v_estado IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El pedido no existe';
  ELSEIF v_estado IN ('enviado', 'entregado', 'cancelado') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El pedido ya no se puede cancelar';
  END IF;

  -- Regresar el stock de cada producto del pedido
  UPDATE productos p
  JOIN detalle_pedido d ON d.producto_id = p.id
  SET p.stock = p.stock + d.cantidad
  WHERE d.pedido_id = p_pedido_id;

  UPDATE pedidos SET estado = 'cancelado' WHERE id = p_pedido_id;

  COMMIT;
END //

DELIMITER ;

sql

CREATE OR REPLACE PROCEDURE cancelar_pedido(p_pedido_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
  v_estado VARCHAR(20);
BEGIN
  SELECT estado INTO v_estado
  FROM pedidos WHERE id = p_pedido_id
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'El pedido % no existe', p_pedido_id;
  ELSIF v_estado IN ('enviado', 'entregado', 'cancelado') THEN
    RAISE EXCEPTION 'El pedido % ya no se puede cancelar (estado: %)', p_pedido_id, v_estado;
  END IF;

  -- Regresar el stock
  UPDATE productos p
  SET stock = p.stock + d.cantidad
  FROM detalle_pedido d
  WHERE d.producto_id = p.id
    AND d.pedido_id = p_pedido_id;

  UPDATE pedidos SET estado = 'cancelado' WHERE id = p_pedido_id;
  -- CALL corre dentro de una transacción: si hay EXCEPTION, todo se revierte
END;
$$;

sql

CALL cancelar_pedido(4);   -- ✔ María: pendiente → cancelado; regresa 1 novela, 1 lámpara y 2 tazas
CALL cancelar_pedido(1);   -- ✘ Error: el pedido ya no se puede cancelar

Después de cancelar el pedido 4:

id	nombre	stock
5	Cien años de soledad	31
6	Lámpara de escritorio	16
7	Taza térmica	52

Triggers

Un trigger se asocia a una tabla y a un evento:

txt

          ┌─ BEFORE ─┐   ┌─ INSERT ─┐
Trigger = │          │ + │ UPDATE   │ + FOR EACH ROW → tu código
          └─ AFTER ──┘   └─ DELETE ─┘

BEFORE: corre antes de guardar; puede modificar o rechazar la fila. Ideal para validar o completar datos.
AFTER: corre después; ideal para auditoría o actualizar otras tablas.
Dentro del trigger tienes NEW (la fila nueva, en INSERT/UPDATE) y OLD (la fila anterior, en UPDATE/DELETE).

Ejemplo 1: auditoría de cambios de precio

Quieres saber quién cambió precios y cuándo. Primero la tabla de historial:

sql

CREATE TABLE historial_precios (
  id INT AUTO_INCREMENT PRIMARY KEY,          -- PostgreSQL: id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
  producto_id INT NOT NULL,
  precio_anterior DECIMAL(10,2) NOT NULL,
  precio_nuevo DECIMAL(10,2) NOT NULL,
  cambiado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  usuario_bd VARCHAR(100)
);

MySQLPostgreSQL

sql

DELIMITER //

CREATE TRIGGER trg_productos_auditar_precio
AFTER UPDATE ON productos
FOR EACH ROW
BEGIN
  IF NEW.precio <> OLD.precio THEN
    INSERT INTO historial_precios (producto_id, precio_anterior, precio_nuevo, usuario_bd)
    VALUES (NEW.id, OLD.precio, NEW.precio, CURRENT_USER());
  END IF;
END //

DELIMITER ;

sql

-- 1. La función del trigger
CREATE OR REPLACE FUNCTION fn_auditar_precio()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.precio IS DISTINCT FROM OLD.precio THEN
    INSERT INTO historial_precios (producto_id, precio_anterior, precio_nuevo, usuario_bd)
    VALUES (NEW.id, OLD.precio, NEW.precio, current_user);
  END IF;
  RETURN NEW;
END;
$$;

-- 2. El trigger que la usa
CREATE TRIGGER trg_productos_auditar_precio
AFTER UPDATE ON productos
FOR EACH ROW
EXECUTE FUNCTION fn_auditar_precio();

sql

UPDATE productos SET precio = 999.00 WHERE id = 1;
SELECT producto_id, precio_anterior, precio_nuevo FROM historial_precios;

producto_id	precio_anterior	precio_nuevo
1	899.00	999.00

Ejemplo 2: descontar stock automáticamente

Cada vez que se inserta una línea en detalle_pedido, se descuenta el stock; si no alcanza, se rechaza.

MySQLPostgreSQL

sql

DELIMITER //

CREATE TRIGGER trg_detalle_descontar_stock
BEFORE INSERT ON detalle_pedido
FOR EACH ROW
BEGIN
  DECLARE v_stock INT;

  SELECT stock INTO v_stock FROM productos WHERE id = NEW.producto_id FOR UPDATE;

  IF v_stock < NEW.cantidad THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuficiente';
  END IF;

  UPDATE productos SET stock = stock - NEW.cantidad WHERE id = NEW.producto_id;
END //

DELIMITER ;

sql

CREATE OR REPLACE FUNCTION fn_descontar_stock()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE productos
  SET stock = stock - NEW.cantidad
  WHERE id = NEW.producto_id
    AND stock >= NEW.cantidad;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Stock insuficiente para el producto %', NEW.producto_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_detalle_descontar_stock
BEFORE INSERT ON detalle_pedido
FOR EACH ROW
EXECUTE FUNCTION fn_descontar_stock();

sql

-- Pedido de prueba con 10 monitores (solo hay 5)
INSERT INTO pedidos (id, cliente_id, fecha) VALUES (7, 5, '2024-07-10');
INSERT INTO detalle_pedido VALUES (7, 3, 10, 3499.00);
-- ERROR: Stock insuficiente

Ejemplo 3: columna updated_at automática (PostgreSQL)

MySQL tiene ON UPDATE CURRENT_TIMESTAMP en la definición de la columna. PostgreSQL necesita un trigger:

sql

ALTER TABLE productos ADD COLUMN actualizado_en TIMESTAMPTZ NOT NULL DEFAULT now();

CREATE OR REPLACE FUNCTION fn_set_actualizado_en()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.actualizado_en := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_productos_actualizado_en
BEFORE UPDATE ON productos
FOR EACH ROW EXECUTE FUNCTION fn_set_actualizado_en();

sql

-- Equivalente en MySQL, sin trigger:
ALTER TABLE productos
  ADD COLUMN actualizado_en TIMESTAMP NOT NULL
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Administrar

sql

-- MySQL
SHOW FUNCTION STATUS WHERE Db = 'tienda';
SHOW PROCEDURE STATUS WHERE Db = 'tienda';
SHOW TRIGGERS;
DROP FUNCTION IF EXISTS precio_con_iva;
DROP PROCEDURE IF EXISTS cancelar_pedido;
DROP TRIGGER IF EXISTS trg_productos_auditar_precio;

-- PostgreSQL (psql: \df funciones, \dy triggers de eventos, \d productos muestra triggers)
DROP FUNCTION IF EXISTS precio_con_iva(NUMERIC);
DROP PROCEDURE IF EXISTS cancelar_pedido(INT);
DROP TRIGGER IF EXISTS trg_productos_auditar_precio ON productos;

¿Lógica en la BD o en la aplicación?

A favor de la BD	En contra
Se aplica sin importar qué app o script modifique los datos	Más difícil de versionar, probar y depurar
Menos viajes de red para operaciones de varios pasos	Lógica "escondida": el equipo no sabe por qué cambió un dato
Auditoría confiable	Te ata al dialecto de un motor (MySQL ≠ PostgreSQL)
	En hosting compartido a veces no hay permiso para crearlos

Buena práctica

Usa triggers para tareas pequeñas y transversales: auditoría, updated_at, mantener un contador o un total desnormalizado. Deja las reglas de negocio complejas (descuentos, flujos de pedido) en la aplicación, donde se prueban y versionan más fácil. Si usas procedimientos, guárdalos en tus migraciones.

Error común: triggers en cascada

Un trigger que actualiza otra tabla que tiene su propio trigger, que actualiza otra... Se vuelve imposible de seguir y puede causar bucles. Mantén los triggers simples y documentados.

Ejercicios

Crea una función nombre_categoria(id) que devuelva el nombre de la categoría o 'Sin categoría' si el id es NULL o no existe.
Crea un procedimiento reabastecer(p_producto_id, p_cantidad) que sume stock y rechace cantidades menores o iguales a 0.
Crea un trigger que impida borrar clientes que tengan pedidos con estado 'pendiente' (aunque la FK ya lo impide en general, practica el mensaje personalizado).

Ver solución

sql

-- 1 (PostgreSQL)
CREATE OR REPLACE FUNCTION nombre_categoria(p_id INT)
RETURNS VARCHAR LANGUAGE sql STABLE AS $$
  SELECT COALESCE((SELECT nombre FROM categorias WHERE id = p_id), 'Sin categoría');
$$;

SELECT nombre, nombre_categoria(categoria_id) FROM productos;

sql

-- 2 (MySQL)
DELIMITER //
CREATE PROCEDURE reabastecer(IN p_producto_id INT, IN p_cantidad INT)
BEGIN
  IF p_cantidad <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La cantidad debe ser positiva';
  END IF;
  UPDATE productos SET stock = stock + p_cantidad WHERE id = p_producto_id;
END //
DELIMITER ;

CALL reabastecer(3, 10);   -- el monitor pasa de 5 a 15

sql

-- 3 (PostgreSQL)
CREATE OR REPLACE FUNCTION fn_proteger_cliente()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM pedidos WHERE cliente_id = OLD.id AND estado = 'pendiente') THEN
    RAISE EXCEPTION 'No puedes borrar a % : tiene pedidos pendientes', OLD.nombre;
  END IF;
  RETURN OLD;   -- en BEFORE DELETE se devuelve OLD para permitir el borrado
END;
$$;

CREATE TRIGGER trg_clientes_proteger
BEFORE DELETE ON clientes
FOR EACH ROW EXECUTE FUNCTION fn_proteger_cliente();

Resumen

Las funciones devuelven un valor y se usan en consultas; los procedimientos ejecutan tareas con CALL.
Los triggers se disparan solos BEFORE/AFTER un INSERT, UPDATE o DELETE, con acceso a NEW y OLD.
MySQL usa DELIMITER y SIGNAL; PostgreSQL usa $$, PL/pgSQL y RAISE EXCEPTION.
Casos ideales: auditoría, updated_at, mantener contadores y validar stock.
Úsalos con moderación: la lógica de negocio compleja suele vivir mejor en la aplicación.

Procedimientos y triggers ​

Concepto ​

Funciones ​

Ejemplo: precio con IVA ​

Ejemplo: total de un pedido ​

Procedimientos almacenados ​

Triggers ​

Ejemplo 1: auditoría de cambios de precio ​

Ejemplo 2: descontar stock automáticamente ​

Ejemplo 3: columna updated_at automática (PostgreSQL) ​

Administrar ​

¿Lógica en la BD o en la aplicación? ​

Ejercicios ​

Resumen ​

Procedimientos y triggers

Concepto

Funciones

Ejemplo: precio con IVA

Ejemplo: total de un pedido

Procedimientos almacenados

Triggers

Ejemplo 1: auditoría de cambios de precio

Ejemplo 2: descontar stock automáticamente

Ejemplo 3: columna updated_at automática (PostgreSQL)

Administrar

¿Lógica en la BD o en la aplicación?

Ejercicios

Resumen