FoxPress – Febrero

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:

DROP DATABASE IF EXISTS MYDB;
CREATE DATABASE MYDB;

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à.