En esta entrada aprenderemos a crear un trigger y función PL/PGSQL para los Insert que se ejecuten en PostgreSQL,
Haremos un trigger que en cada operación de inserción en una tabla especifica de nuestra base de datos PostgreSQL ejecute una función hecha en PL/PGSQL, la cual generara é insertara en un campo de nuestra tabla seleccionada un código compuesto por el año actual, el valor de un campo (siglas de un estado) en una tabla relacionada y un incremental que debe ser único para los parámetros previos.
Es decir el código debe ser como 2015AM0006 2015SU0001 2015AM0007 y se debe comportar de tal manera que cada año se inicialice el valor incremental y dicho valor se incrementa en base al año y siglas de estado.
Funciones y Triggers en PostgreSQL
Como crear un trigger y función PL/PGSQL para Insert en PostgreSQL
Crear Trigger que ejecute una función en PostgreSQL
[sourcecode language="sql"] CREATE TRIGGER trigger_codigo_compuesto BEFORE INSERT ON obra FOR EACH ROW EXECUTE PROCEDURE crear_codigo_compuesto() [/sourcecode]
Indica que ejecutara la función crear_codigo_compuesto cada vez que se inserte un registro en la tabla «obra».
Función que genera código compuesto por AÑO ACTUAL+ SIGLA DE ESTADOS + NUMERO INCREMENTAL
[sourcecode language="sql"] CREATE OR REPLACE FUNCTION crear_codigo_compuesto() RETURNS trigger AS ' DECLARE anio text; siglas_estado text; incremental text; BEGIN IF NEW.campocodigocompuesto IS NULL OR NEW.campocodigocompuesto = '''' THEN anio = ( SELECT (date_part(''year'', NOW())::text) ); siglas_estado = ( select siglas from estado where id_estado = NEW.fk_estado ); incremental = ( select to_char( ( max(substring(campocodigocompuesto,7,4))::integer + 1), ''FM0999'') from obra where anio = substring(campocodigocompuesto,0,5) AND substring(campocodigocompuesto,5,2) = siglas_estado ); NEW.campocodigocompuesto := anio || siglas_estado || incremental; END IF; RETURN NEW; END' LANGUAGE 'plpgsql' [/sourcecode]
Explicación de la función PostgreSQL
- En las líneas 4, 5, 6 declaramos variables de tipo texto.
- La línea 8 valida que el campo «campocodigocompuesto» de la tabla obra sea nulo ó este vacío.
- En la línea 9 extraemos el año usando date_part de la fecha actual que capturamos usando now() y lo guardamos en la variable anio. Vea las funciones de fecha y hora que tiene postgres http://www.postgresql.org/docs/9.4/static/functions-datetime.html
- En la línea 10 buscamos las siglas en la tabla relacionada «estado».
- Línea 12. Seleccionaremos de la tabla obra aquellos registros cuyo campocodigocompuesto correspondan al año en curso y que correspondan al estado que se pretende insertar al momento de ejecutar el insert que llama a esta función. Para esto usamos la función substring con lo cual extraemos del código el año sabiendo que este se encontrara en los primeros cuatro caracteres del campo y las siglas que se son el sexto y séptimo carácter.
- Línea 11. Teniendo ya todos los registros usaremos la función max que nos retornara el valor máximo de los valores incrementales que obtenemos con la función substring que nos extraerá solamente los valores incrementales del campocodigocompuesto. Hacemos una conversión a tipo integer para adicionar un valor, con lo cual ya tenemos el valor que deberemos insertar y le damos formato de rellenar ceros a la izquierda con la función to_char. El resultado lo guardamos en la variable «incremental»
- En la línea 13 finalmente le asignaremos el valor a nuestro campo objetivo en la inserción (NEW.campocodigocompuesto), para ello concatenamos las variables que hemos trabajado previamente.
El resultado final en pgAdmin. Códigos generados automáticamente al realizar insert sobre la tabla objetivo.
Podemos ver que se probo sobre dos estados Monagas y Amazonas (MO y AM), también que existiendo un registro del mismo estado y igual próximo valor secuencial, se realiza discriminación por el año actual (últimos tres registros).
En pocas lineas realizamos un trabajo de calidad, que garantiza la integridad del código compuesto que la regla del negocio requiere, para ello usamos PL/PGSQL y varias funciones de PostgreSQL, en otro escenario generar e insertar este código desde el aplicativo hubiera resultado tedioso, engorroso e ineficiente.
CREATE OR REPLACE FUNCTION tgcompras()
RETURNS trigger AS
$compras$
begin
IF(NEW.cantidad_producto>0) then
UPDATE inventario set saldo=saldo+new.cantidad_producto where id_inventario=1;
UPDATE compras_totales set saldo= saldo+new.monto where id_compra_total=1;
END IF;
IF(NEW.tipo_de_pago=’efectivo’) then
INSERT INTO asientos_de_compra (consepto,efectivo,credito,cuenta,fecha) values(new.detalle_producto,new.monto,0,’CAJA’,now());
update cuentas set saldo=saldo+new.monto where id_cuenta=3;
UPDATE cuentas set saldo=saldo-new.monto where id_cuenta=1;
END IF;
IF (NEW.tipo_de_pago=’credito’) then
INSERT INTO asientos_de_compra (consepto,efectivo,credito,cuenta,fecha) values(new.detalle_producto,0,new.monto,’CUENTAS POR COBRAR’,now());
UPDATE cuentas set saldo=saldo-new.monto where id_cuenta=2;
update cuentas set saldo=saldo+new.monto where id_cuenta=3;
END IF;
return new;
END;
$compras$ LANGUAGE PLPGSQL;
create trigger tgcompras after insert or update on compras for each row
execute procedure tgcompras();
insert into compras(id_inventario,id_compra_total,id_gasto_de_compra,id_asiento_de_compra,id_cuenta,detalle_producto,marca_producto,cantidad_producto,
precio_producto,monto,tipo_de_pago,fecha_compra) values
(1,1,1,1,1,’abc’,’xface’,2,100,200,’efectivo’,now());
–CREAR UN TRIGGER PARA UNA VENTA
CREATE OR REPLACE FUNCTION tgventas() RETURNS TRIGGER AS $ventas$
begin
IF(NEW.cantidad_producto>0) then
UPDATE inventario set saldo=saldo-new.cantidad_producto where id_inventario=1;
update inventario set saldo=saldo+new.cantidad_producto where id_inventario=2;
update ventas_totales set saldo=saldo+new.monto where id_venta_total=1;
END IF;
IF(new.tipo_de_pago=’efectivo’) THEN
insert into asientos_de_venta(consepto,efectivo,cuenta,fecha) values(new.detalle_producto,new.monto,’CAJA’,now());
UPDATE cuentas set saldo=saldo+new.monto where id_cuenta=4;
update cuentas set saldo=saldo+new.monto where id_cuenta=6;
END IF;
IF(new.tipo_de_pago=’credito’) THEN
insert into asientos_de_venta(consepto,credito,cuenta,fecha) values(new.detalle_producto,new.monto,’CUENTAS POR COBRAR’,now());
UPDATE cuentas set saldo=saldo+new.monto where id_cuenta=5;
update cuentas set saldo=saldo+new.monto where id_cuenta=6;
END IF;
RETURN NEW;
end;
$ventas$ LANGUAGE PLPGSQL;
create trigger tgventas after insert or update on ventas for each row
execute procedure tgventas();
insert into ventas(id_cliente,id_inventario,id_venta_total,id_gasto_de_venta,id_cuenta,id_asiento_de_venta,
detalle_producto,marca_producto,cantidad_producto,precio_producto,monto,tipo_de_pago,fecha_venta) values
(1,1,1,1,1,1,’abc’,’xface’,2,300,600,’efectivo’,now());
Espero que les sirva este codigo
Excelente Jhony estoy seguro nos servirá a muchos!