Triggers en MySQL
Por
Antonio L. Montagut
Desde la aparición de la
versión 5.0.3 de la base de datos MySQL, se ha implementado la posibilidad de
desarrollar triggers para las tablas
tal y como ya lo podíamos hacer en nuestro querido Fox. Recordar que un trigger es un objeto
relacionado a una tabla que es ejecutado cuando sucede un evento en la tabla a
la que va asociado. Son aquellas sentencias (INSERT, UPDATE, DELETE) que modifican los datos dentro de una
tabla.
De momento el código que podemos crear para el desarrollo de triggers en MySQL,
es más encorsetado que el código que utilizamos en fox. Dentro de un trigger
MySQL,no podemos hacer referencia por ejemplo a otra tabla mediante una orden
SELECT, cosa que si podemos hacer en Fox.
Pero los triggers en MySQL
tienen una ventaja obvia sobre los de Fox. Es código que se ejecuta en el
servidor de la base de datos, y no en la máquina cliente como pasa en Fox.
Para no extendernos mucho, y como un buen ejemplo vale mas que mil palabras,
vamos ha explicar el manejo de triggers mediante un sencillo ejemplo, que va ha
consistir en una base de datos con 2 tablas. Una de apuntes contables y otra de
saldos mensuales que se irán actualizando tal y como vayamos insertando,
modificando o eliminado apuntes.
Para crear código de MySQL
aconsejo utilizar un editor de texto, puede servir perfectamente el Notepad de
Windows. Pero yo recomiendo que utiliceis el PSPAD, es free, trabaja con
múltiples pestañas, tiene corrector ortográfico para múltiples lenguajes de
programación y está disponible en castellano. Se puede descargar de www.pspad.com
Creamos un archivo de texto
que le llamaremos EJEMPLO1.SQL Que será el cual almacenará nuestro código
SQL que de ahora en adelante pondré en negrita.
- Creamos la base de datos:
USE MYDB;
- Crearemos
las tablas de apuntes y saldos del tipo InnoDB, con una clave primaria para
identificar cada registro de la tabla como único.
DROP TABLE IF EXISTS APUNTES;
CREATE
TABLE APUNTES (
ASIENTO
INT(8) DEFAULT 0,
LINEA
SMALLINT(5) DEFAULT 0,
FECHA DATE DEFAULT ‘2006-01-01’,
TEXTO VARCHAR(40) default '',
CUENTA CHAR(10) default '',
DEBE DOUBLE(10,2) DEFAULT 0,
HABER
DOUBLE(10,2) DEFAULT 0,
PRIMARY
KEY (ASIENTO,LINEA),
KEY K2(CUENTA, FECHA) )
ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
- Creamos la tabla de
Saldos, también con una clave primaria única.
DROP TABLE IF EXISTS SALDO;
CREATE
TABLE SALDO (
CUENTA
CHAR(10) NOT NULL default '',
ANO
SMALLINT(4) DEFAULT 0,
MES
TINYINT(2) DEFAULT 0,
DEBE
DOUBLE(10,2) DEFAULT 0,
HABER
DOUBLE(10,2) DEFAULT 0,
PRIMARY KEY (CUENTA,ANO,MES) )
ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
- Vamos a proceder a crear
los Triggers, para la tabla de apuntes.
- Primero el trigger de
inserción de registros.
Atención a los delimitadores y al punto y coma de final de sentencia.
Remarcar que los saldos se actualizan después de entrar un registro en la tabla
de apuntes (AFTER INSERT) .
Muy interesante es la orden: INSERT INTO
... ON
DUPLICATE KEY UPDATE.
Esta sentencia
inserta un registro en la tabla de saldos, y si este existiera, actualiza
solamente las columnas debe y haber. Por eso hemos definido claves primarias (PRIMARY KEY) en las tablas, requisito
indispensable para que esta sentencia funcione.
DELIMITER //
CREATE TRIGGER APTS_I AFTER INSERT ON APUNTES
FOR EACH ROW
BEGIN
INSERT INTO SALDO SET
SALDO.CUENTA=NEW.CUENTA,
SALDO.ANO=YEAR(NEW.FECHA),
SALDO.MES=MONTH(NEW.FECHA),
SALDO.DEBE=NEW.DEBE,
SALDO.HABER=NEW.HABER
ON
DUPLICATE KEY UPDATE
SALDO.DEBE=SALDO.DEBE+NEW.DEBE,
SALDO.HABER=SALDO.HABER+NEW.HABER
;
END;//
DELIMITER ;
- Creamos el trigger de actualización o modificación
de asientos:
Es importante resaltar las equivalencias de código:
MySQL |
FoxPro |
OLD.DEBE |
OLDVAL(“DEBE”,”APUNTES”) |
NEW.DEBE |
CURVAL(“DEBE”,”APUNTES”) |
DELIMITER //
CREATE TRIGGER APTS_U AFTER UPDATE ON APUNTES
FOR EACH ROW
BEGIN
INSERT
INTO SALDO SET
SALDO.CUENTA=OLD.CUENTA,
SALDO.ANO=YEAR(OLD.FECHA),
SALDO.MES=MONTH(OLD.FECHA),
SALDO.DEBE=OLD.DEBE*(-1),
SALDO.HABER=OLD.HABER*(-1)
ON
DUPLICATE KEY UPDATE
SALDO.DEBE=SALDO.DEBE+(OLD.DEBE*(-1)),
SALDO.HABER=SALDO.HABER+(OLD.HABER*(-1))
;
INSERT INTO SALDO SET
SALDO.CUENTA=NEW.CUENTA,
SALDO.ANO=YEAR(NEW.FECHA),
SALDO.MES=MONTH(NEW.FECHA),
SALDO.DEBE=NEW.DEBE,
SALDO.HABER=NEW.HABER
ON
DUPLICATE KEY UPDATE
SALDO.DEBE=SALDO.DEBE+NEW.DEBE,
SALDO.HABER=SALDO.HABER+NEW.HABER ;
END;//
DELIMITER ;
- Por último creamos el trigger de eliminación de apuntes
DELIMITER //
CREATE TRIGGER APTS_D AFTER DELETE ON APUNTES
FOR EACH ROW
BEGIN
INSERT
INTO SALDO SET
SALDO.CUENTA=OLD.CUENTA,
SALDO.ANO=YEAR(OLD.FECHA),
SALDO.MES=MONTH(OLD.FECHA),
SALDO.DEBE=OLD.DEBE*(-1),
SALDO.HABER=OLD.HABER*(-1)
ON
DUPLICATE KEY UPDATE
SALDO.DEBE=SALDO.DEBE+(OLD.DEBE*(-1)),
SALDO.HABER=SALDO.HABER+(OLD.HABER*(-1))
;
END;//
DELIMITER ;
- Grabamos el archivo EJEMPLO1.SQL
- Vamos al prompt del MySQL
con el usuario root. Para estar seguros de tener todos los privilegios
necesarios, y ejecutamos el código de ejemplo.
- Vamos ahora a crear un
archivo de código MySQL que le denominaremos EJEMPLO2.SQL y que va ha contener 2 asientos contables.
Correspondientes a una factura y el cobro de la misma al mes siguiente.
Utilizaremos transacciones.
USE MYDB;
SET AUTOCOMMIT=0 ;
START TRANSACTION ;
INSERT INTO APUNTES VALUES (1,1,'2006-02-07','Fra.112
PEPE PALO','4300000001',1160,0);
INSERT INTO APUNTES VALUES
(1,2,'2006-02-07','Fra.112 PEPE PALO','4770000001',0,160);
INSERT INTO APUNTES VALUES
(1,3,'2006-02-07','Fra.112 PEPE PALO','7000000000',0,1000);
INSERT INTO APUNTES VALUES (2,1,'2006-03-20','Cobro
Fra.112 PEPE PALO','5700000000',1160,0);
INSERT INTO APUNTES VALUES (2,2,'2006-03-20','Cobro Fra.112 PEPE
PALO','4300000001',0,1160);
COMMIT;
Grabamos el archivo EJEMPLO2.SQL . Vamos
al prompt de MySQL y ejecutamos el código.
Ahora hacemos un SELECT de la tabla
SALDOS y ...Voilà.