Triggers en POSTGRES

Estaba haciendo un TRIGGER en POSTGRES, que prácticamente es lo mismo que hacerlo en los demás gestores de base de datos más utilizados por los desarrolladores que yo conozco es decir SQL SERVER y MYSQL. Pero no hay nada mejor que aprender con un ejemplo.


El caso es el siguiente estamos haciendo un módulo para realizar un inventario físico de existencias en un almacén, el inventario físico es en palabras sencillas hacer una comparación entre las existencias que tiene registradas el sistema y las existencias que hay realmente en físico en la bodega, para después hacer los ajustes necesarios en el sistema.

Dicho esto tenemos las siguientes tablas.

CREATE TABLE in_existencias
(
bodega_id character(15) NOT NULL,
producto_id character(10) NOT NULL,
existencia_inicial integer NOT NULL DEFAULT 0,
entrada integer NOT NULL DEFAULT 0,
salida integer NOT NULL DEFAULT 0,
existencia integer NOT NULL DEFAULT 0,
existencia_congelada integer NOT NULL DEFAULT 0,
CONSTRAINT pk_in_existencias_bodega_id_producto_id PRIMARY KEY (bodega_id, producto_id)
);

CREATE TABLE in_productos(
producto_id character(10) NOT NULL,
descripcion character varying(50),
costo_unitario numeric(12,4) NOT NULL DEFAULT 0,
CONSTRAINT pk_in_productos_producto_id PRIMARY KEY (producto_id)
);

CREATE TABLE in_enc_inventarios_fisicos
(
punto_venta_id character(15) NOT NULL,
naturaleza character(3) NOT NULL,
tipo_documento character(3) NOT NULL,
documento_no integer NOT NULL,
fecha date NOT NULL,
bodega_id character(15),
encargado character(75) NOT NULL,
estatus character(1) NOT NULL, 
CONSTRAINT pk_in_enc_inventarios_fisicos PRIMARY KEY (punto_venta_id, naturaleza, tipo_documento, documento_no)
);

CREATE TABLE in_lns_inventarios_fisicos
(
punto_venta_id character(15) NOT NULL,
naturaleza character(3) NOT NULL,
tipo_documento character(3) NOT NULL,
documento_no integer NOT NULL,
linea serial NOT NULL,
producto_id character(10) NOT NULL,
cantidad integer NOT NULL DEFAULT 0,
bodega_id character(15) NOT NULL,
existencia integer NOT NULL,
costo numeric(12,4) NOT NULL,
importe numeric(18,4) NOT NULL,
fechahora timestamp without time zone,
comentario_ajuste text DEFAULT ''::character varying,
cantidad_primera_aplicacion integer NOT NULL DEFAULT 0,
CONSTRAINT pk_in_lin_inventarios_fisicos PRIMARY KEY (punto_venta_id, naturaleza, tipo_documento, documento_no, linea)
);

Bueno esas son las tablas que utilice para el trigger, vale mencionar que le quite varios campos y llaves para que el ejemplo se más fácil y más corto. Bueno ahora veamos lo que más nos interesa que es lo que va hacer nuestro trigger, pues bien el trigger tiene como objetivo llenar la tabla "in_lns_inventarios_fisicos" con todos cuando se ingrese un registro en la tabla "in_enc_inventarios_fisicos", la idea es esta cuando alguien inserte una linea en la tabla de "in_enc_inventarios_fisicos" tiene que ingresar la bodega, entonces el trigger debe ingresar en la tabla de "in_lns_inventarios_fisicos" todos los productos que tengan existencias en esa bodega, es decir que inserte todos los productos que estén en la tabla "in_existencias" espero que no los confunda con esto, pero creo que viendo el código del trigger lo entienden más fácil, la definición del trriger es la siguiente.

CREATE OR REPLACE FUNCTION tgfn_in_enc_inventarios_fisicos()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN

INSERT INTO in_lns_inventarios_fisicos(punto_venta_id, naturaleza, tipo_documento, documento_no, producto_id, bodega_id, existencia, costo, importe, fechahora)
SELECT NEW.punto_venta_id,
NEW.naturaleza,
NEW.tipo_documento,
NEW.documento_no,
E.producto_id AS producto_id,
NEW.bodega_id,
E.existencia AS existencia,
P.costo_unitario AS costo,
(E.existencia * P.costo_unitario) AS importe,
NEW.fecha AS fechahora
FROM in_existencias E, in_Productos P
WHERE E.bodega_id = NEW.bodega_id
AND P.producto_id = E.producto_id;

END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

Entonces todo lo anterior se reduce a esas pocas lineas, hay muchas cosas que obviare porque no se que hacen o porque no vale la pena mencionar, lo primero que vale mencionar es la linea siguiente.

IF (TG_OP = 'INSERT') THEN

Lo que hace esa linea es validar la operación por la que se esta llamando el trigger, entonces en nuestro caso es por una operación de "INSERT" en la tabla "in_enc_inventarios_fisicos", bueno esta es una de las primeras diferencias con los otros gestores de bases de datos, ya  en postgres se define el trigger para todas las operaciones en una sola función, y en SQL SERVER y MySQL se hace una para cada operación por ejemplo se hace un trigger para el insert, otro para el update y otro delete.

Ahora llegamos a lo más interesante el cuerpo de nuestro trigger.

INSERT INTO in_lns_inventarios_fisicos(punto_venta_id, naturaleza, tipo_documento, documento_no, producto_id, bodega_id, existencia, costo, importe, fechahora)
SELECT NEW.punto_venta_id,
NEW.naturaleza,
NEW.tipo_documento,
NEW.documento_no,
E.producto_id AS producto_id,
NEW.bodega_id,
E.existencia AS existencia,
P.costo_unitario AS costo,
(E.existencia * P.costo_unitario) AS importe,
NEW.fecha AS fechahora
FROM in_existencias E, in_Productos P
WHERE E.bodega_id = NEW.bodega_id
AND P.producto_id = E.producto_id;

Lo anterior no es nada más que un "INSERT" desde un "SELECT" simplemente que lo estamos haciendo desde un trigger, entonces comencemos a entenderlo, primero el insert con el listado de campos en la tabla de "in_lns_inventarios_fisicos".

Ahora vamos con el select, el select obtiene todos los productos y sus datos de las tablas de "in_existencias" y "in_productos" de la bodega insertada en la tabla "in_enc_inventarios_fisicos". Ahora profundicemos en la sintaxis del select, lo primero que puede que no todos conozcan y es esa palabra reservada "NEW", pues NEW es lo mismo que INSERTED y DELETED en SQL SERVER entonces es con NEW que hacemos referencia a los valores que se están insertando en la tabla "in_enc_inventarios_fisicos" que son los que desencadenan la ejecución del trigger, es por esto que se ocupan todos los datos del encabezado que identifican al inventario fisico es decir los campos de punto_venta_id, naturaleza, tipo_documento, documento_no, bodega_id, fecha todos esos datos se obtienen con NEW y los demas campos se obtienen del resultado del select y esos son los campos producto_id, existencia, costo_unitario, importe(campo calculado), lo que sigue es el where aqui vemos de nuevo la utilización de NEW para el campo bodega_id y la utilización de alias de tablas para lograr el JOIN de la tabla in_existencias y in_productos.

Y eso es todo ahí esta nuestro trigger, dejo a su imaginación la interfaz y todo eso.
FINITO.