Crear trigger y funci贸n PL/PGSQL que genere c贸digo compuesto al realizar Insert en PostgreSQL

trigger y funci贸n PL/PGSQL para Insert en PostgreSQL

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.

trigger funcion postgres codigo compuesto insertEl 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.

Tambi茅n te podr铆a gustar...

4 Respuestas

  1. jhony dice:

    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());

  2. jhony dice:

    –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());

  3. jhony dice:

    Espero que les sirva este codigo

  4. leninmhs dice:

    Excelente Jhony estoy seguro nos servir谩 a muchos!

Deja un comentario

Tu direcci贸n de correo electr贸nico no ser谩 publicada. Los campos obligatorios est谩n marcados con *