tables:

-> detalle_recepcion_merk
   |-> trigger: add_inventario_recepcion AFTER INSERT ON detalle_recepcion_merk FOR EACH ROW EXECUTE PROCEDURE add_rec_inventario()


# function add_rec_inventario:

CREATE OR REPLACE FUNCTION public.add_rec_inventario()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
        begin
                -- inventario: id_movto | id_cedis | id_tipo_movto | id_producto | folio_movto | lote | cantidad_entrada | cantidad_salida | id_usuario | fecha_add
                --
                insert into inventario (id_cedis, id_tipo_movto, id_producto, folio_movto, lote, cantidad_entrada, cantidad_salida, id_usuario, fecha_add) values (NEW.id_cedis, 1, NEW.id_producto, NEW.id_recepcion, NEW.lote, NEW.cantidad, 0, NEW.id_usuario, current_timestamp);
                return NEW;
                exception when others then
                        -- log if fails;
                        insert into function_logs(message,fecha_add) values('ERR: add_rec_inventario ' || SQLERRM, current_timestamp);

        end;
$function$




-> inventario
   |-> trigger : manage_lote AFTER INSERT ON inventario FOR EACH ROW EXECUTE PROCEDURE manage_lotes()

# function manage_lotes():

CREATE OR REPLACE FUNCTION public.manage_lotes()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare orden integer;
        begin
                select coalesce(max(priority),0) into orden from detalle_lotes where id_producto = NEW.id_producto;
                if (NEW.id_tipo_movto = 1) then
                        -- add by recepcion merk
                        insert into detalle_lotes values (NEW.id_producto, NEW.lote, NEW.cantidad, NEW.cantidad, orden+1);
                end if;
                return NEW;

                exception when others then
                        insert into function_logs(message, fecha_add) values('ERR @ manage_lotes: ' || SQLERRM, current_timestamp);

        end;
$function$

-----------------------------------------------

ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function add_rec_inventario()