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
CREATE TRIGGER trigger_codigo_compuesto BEFORE INSERT ON obra FOR EACH ROW EXECUTE PROCEDURE crear_codigo_compuesto()
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
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'
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!