Curso de MariaDB / Usar procedimientos almacenados, funciones y triggers
Un procedimiento almacenado se invoca expresamente con CALL, una función almacenada devuelve un valor dentro de una expresión SQL y un disparador o trigger se ejecuta automáticamente ante un cambio en una tabla. Aunque los tres guardan lógica SQL en el servidor, difieren en su invocación, sus permisos y su relación con las transacciones.
Elegir el objeto según su función
| Objeto | Invocación | Uso habitual |
|---|---|---|
| Procedimiento almacenado | CALL nombre(argumentos) | Operación explícita con varias sentencias |
| Función almacenada | nombre(argumentos) en una expresión | Regla reutilizable que devuelve un valor |
| Trigger | Automática ante INSERT, UPDATE o DELETE | Acción breve por fila vinculada al cambio de una tabla |
Un procedimiento no está obligado a modificar datos y una función almacenada no se limita universalmente a cálculos aritméticos. Este ejemplo asigna tareas concretas: el procedimiento cambia existencias, la función calcula un importe y el trigger registra las existencias anterior y nueva. En MariaDB el trigger actúa por fila; una sentencia que cambie muchas filas también genera el trabajo correspondiente.
Compare la gestión de COMMIT y ROLLBACK por la conexión que llama con el contexto de seguridad de las vistas. La lógica automática puede dificultar el diagnóstico: distinga la operación que la aplicación invoca de forma deliberada de la que acompaña cada escritura coincidente. Decida qué reglas pertenecen a la validación de la aplicación y cuáles deben aplicarse a distintas rutas de escritura.
Preparar permisos y dos tablas InnoDB
Use appops_lab con una cuenta de prácticas que disponga de CREATE ROUTINE, TRIGGER y los permisos de tabla necesarios. Invocar las rutinas requiere EXECUTE. Revise la guía de cuentas y concesiones en lugar de conceder administración global a la aplicación. La política de registro binario puede añadir requisitos para crear funciones; solicite su revisión en lugar de debilitar opciones globales de confianza para ejecutar el ejemplo.
Compruebe el servidor y las bases y objetos existentes antes de la preparación. Si appops_lab contiene otras prácticas, consérvelas. Revise que las dos tablas y los nombres de rutinas y trigger siguientes no estén ocupados. La preparación no elimina objetos existentes.
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
product_id INT NOT NULL PRIMARY KEY,
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
old_stock INT NOT NULL,
new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);
Ambas tablas utilizan InnoDB. Esto permite cancelar juntos el cambio de existencias y la inserción del registro realizada por el trigger. Si un nombre ya existe, inspeccione su definición y datos antes de decidir cómo continuar.
Crear las tres definiciones completas
El script utiliza la sintaxis del cliente de línea de comandos mariadb. DELIMITER indica al cliente cómo reconocer el final de una sentencia; no es una sentencia SQL del servidor. En un editor gráfico, utilice su ejecución de scripts. Con un controlador Python, envíe cada CREATE completo sin las líneas DELIMITER ni los terminadores //.
DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
END IF;
RETURN ROUND(p_price * p_qty, 2);
END//
CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
IF p_delta IS NULL OR p_delta = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
END IF;
UPDATE routine_stock SET stock = stock + p_delta
WHERE product_id = p_product_id AND stock + p_delta >= 0;
IF ROW_COUNT() <> 1 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
END IF;
END//
CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
IF OLD.stock <> NEW.stock THEN
INSERT INTO routine_audit (product_id, old_stock, new_stock)
VALUES (NEW.product_id, OLD.stock, NEW.stock);
END IF;
END//
DELIMITER ;
line_total rechaza precios o cantidades negativos o NULL y devuelve un importe con dos decimales. La cantidad cero está admitida por esta función. Revise el intervalo DECIMAL para cantidades o precios grandes. DETERMINISTIC NO SQL describe esta implementación y debe seguir siendo cierto si la cambia. La referencia oficial de CREATE FUNCTION explica sus características y seguridad.
adjust_stock modifica un solo producto existente si el resultado no es negativo; en otro caso genera un error de negocio con SIGNAL. Comprueba ROW_COUNT inmediatamente después de UPDATE, porque otra sentencia puede cambiar a qué operación se refiere. La referencia oficial de CREATE PROCEDURE explica parámetros y contexto de invocación.
El trigger inserta un registro únicamente si cambian las existencias. OLD y NEW representan los valores anterior y nuevo de la fila. Esta tabla registra cambios como práctica; no constituye por sí sola un sistema de auditoría independiente e inmune a manipulaciones. La referencia oficial de CREATE TRIGGER documenta esos valores y el momento AFTER UPDATE.
Comparar ambas tablas al revertir y confirmar
SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;
El importe calculado es 37.50. Dentro de la transacción, las existencias pasan de ocho a cinco y el registro recoge 8 → 5. Después de ROLLBACK, las existencias deben volver a ocho y el número de registros de auditoría a cero. El identificador autoincremental no tiene por qué reutilizarse: un salto de numeración no demuestra que la reversión fallara.
START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;
Tras COMMIT, las existencias son seis y hay un registro 8 → 6. El procedimiento no contiene COMMIT interno, lo que permite a quien lo llama combinarlo con otras operaciones. El trigger comparte el contexto transaccional de la sentencia que lo activa y no confirma por separado.
Observar entradas inválidas y límites de permisos
Ejecute estas sentencias por separado. La primera intenta restar más que las seis unidades actuales y la segunda utiliza un precio negativo. Ambas deben generar SQLSTATE 45000. Después compruebe que las existencias siguen en seis y la tabla conserva su único registro confirmado.
CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);
La función y el procedimiento declaran SQL SECURITY INVOKER, por lo que los accesos a tablas se comprueban con los permisos de quien los invoca. Conceder EXECUTE sin UPDATE no permite modificar existencias mediante este procedimiento. Un trigger utiliza en cambio el contexto de su DEFINER y no se declara INVOKER. Gestione la cuenta definidora, su conservación y los permisos que necesita.
Las limitaciones oficiales de los triggers restringen acciones como controlar transacciones y volver a modificar la tabla que activa la ejecución. Mantenga el trabajo breve y vinculado a los datos de la transacción, sin ocultar operaciones externas largas tras un cambio de fila.
Inspeccionar definiciones y retirar solo los objetos de prácticas
SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;
Guarde las definiciones y los contextos de seguridad y compruebe que su copia y restauración incluyen las rutinas y triggers necesarios. Una definición restaurada en otro servidor puede fallar si falta su cuenta DEFINER o sus permisos. Cuando termine este ejercicio aislado, elimine únicamente sus objetos:
DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;
Un ROLLBACK ordinario no recupera los objetos eliminados. Confirme que esos nombres corresponden a prácticas prescindibles y conserve las demás tablas de appops_lab. Cambiar rutinas de una aplicación exige además coordinar a sus consumidores y disponer de un procedimiento de despliegue y recuperación.




