Trigger Insert in the same table

Asked

Viewed 52 times

0

Hello, I have a table where I keep records of movements, it is inserted a record where it is possible to inform a payment installment, for example, a purchase made in 12 times whose current installment (which was manually inserted in the bank) is the installment 8/12, missing still 4 installments, so when entering this record Trigger must enter the records of installments 9, 10, 11 and 12, all other data must be the same, being only the number of the different installment, follows the code I did:

CREATE FUNCTION fn_parcelas_futuras()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
    IF NEW.quantidade_parcelas > 1 THEN
        FOR mes_parcela IN NEW.parcela_atual..NEW.quantidade_parcelas LOOP
            INSERT INTO movimentacoes(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                parcela_atual,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura
            )
                VALUES(
                    NEW.operacao,
                    NEW.nome_operacao,
                    NEW.id_estabelecimento,
                    NEW.id_cartao,
                    mes_parcela,
                    NEW.quantidade_parcelas,
                    NEW.valor_parcela,
                    NEW.mes_fatura
                );
        END LOOP;
    END IF;
    RETURN NEW;
END;
$$

CREATE TRIGGER tg_inserir_parcelas_futuras
AFTER INSERT
ON movimentacoes
FOR EACH ROW
EXECUTE PROCEDURE fn_parcelas_futuras();

When I try to enter some data I get the following error:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."cartoes" x WHERE "codigo" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
SQL statement "INSERT INTO movimentacoes(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                parcela_atual,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura
            )
                VALUES(
                    NEW.operacao,
                    NEW.nome_operacao,
                    NEW.id_estabelecimento,
                    NEW.id_cartao,
                    mes_parcela,
                    NEW.quantidade_parcelas,
                    NEW.valor_parcela,
                    NEW.mes_fatura
                )"
PL/pgSQL function fn_parcelas_futuras() line 5 at SQL statement
SQL statement "INSERT INTO movimentacoes(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                parcela_atual,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura
            )
                VALUES(
                    NEW.operacao,
                    NEW.nome_operacao,
                    NEW.id_estabelecimento,
                    NEW.id_cartao,
                    mes_parcela,
                    NEW.quantidade_parcelas,
                    NEW.valor_parcela,
                    NEW.mes_fatura
                )"
[...]
REPETE ULTIMO BLOCO ALGUMAS VEZES.
[...]

PL/pgSQL function fn_parcelas_futuras() line 5 at SQL statement
SQL state: 54001
sql postgresql function trigger sql-insert

  • "Trigger must enter the records of plots 9, 10, 11 and 12" Trigger doesn’t quite have that purpose, you need to make a procedure which is the most correct object to do this kind of operation

  • Right, in case the trigger should call the procedure so that’s it?

  • The Trigger gets recursive (stack Depth limit exceeded) but this logic is not making sense , what entered the "8" ?! A file must be entered in tranche 1 by "n".

  • @bruno101 would be better for all the code in the Trial without Rigger, does all the Inserts in "drives" in a single process, clear and well defined, even can be transactional, commit when generating all the plots, does not need a Trigger

  • @Motta I mentioned there, the parcel 8 would have been inserted manually, then the bank itself should insert the remaining installments, if it had been inserted the parcel 1/12 all the others should be inserted, if it were the 12/12 nothing would be done, because it is already the last installment.

  • @Ricardopunctual but the procedure would not need a trigger to be called? Even if the whole process is done in the procedure.

  • The problem is the Rigger is recursive , will insert for example from 8 to 12 , when inserting the 8 the Rigger inserts in the table and fires from 9 to 12 and so on , the Rigger does not seem to fit here in this case.

  • @bruno101 "the project would not need a Rigger to be called" of course not, if it were so, for the whole trial would need a Rigger :) instead of running a insert executes the precedent that will make all the Inserts at once, will change only that in the way to make the call

  • @Motta understood what you meant, in fact, from the call 9 to 12 we would have triggers being fired accordingly at each new insert, in fact this is not the desired behavior.

  • @Ricardopunctual in this case I would make a procedure to enter data, this would be responsible for assessing the situation and need for entering the other data. Would that be? Something like: pr_insert_movimentacoes(<parâmetros do insert aqui>)

  • 1

    that, then you can control everything in the same process

Show 6 more comments

1 answer

0


According to @Ricardopunctual and @Motta, the solution I was developing would not work, as it would end up falling into a recursion of the query, the resolution was indicated by means of a procedure where I would only make the call of the same, I do not know if the execution was the best, I believe that it can improve, but follows the code developed.

CREATE OR REPLACE PROCEDURE PR_INSERT_MOVIMENTACOES(
    operacao bool,
    nome_operacao VARCHAR(255),
    id_estabelecimento INTEGER,
    id_cartao INTEGER,
    parcela_atual INTEGER,
    quantidade_parcelas INTEGER,
    valor_parcela NUMERIC(12,2),
    mes_fatura INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    mes_fatura_parcela INTEGER := mes_fatura;
BEGIN
    IF parcela_atual < quantidade_parcelas THEN
        FOR mes_parcela IN parcela_atual..quantidade_parcelas LOOP
            INSERT INTO movimentacoes(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                parcela_atual,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura)
            VALUES(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                mes_parcela,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura_parcela);

            mes_fatura_parcela := mes_fatura_parcela + 1;

            IF mes_fatura_parcela > 12 THEN
                mes_fatura_parcela := 1;
            END IF;
            
        END LOOP;
        ELSE
            INSERT INTO movimentacoes(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                parcela_atual,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura)
            VALUES(
                operacao,
                nome_operacao,
                id_estabelecimento,
                id_cartao,
                parcela_atual,
                quantidade_parcelas,
                valor_parcela,
                mes_fatura);
    END IF;
    
    COMMIT;
END;$$

With this just make the following call:

CALL PR_INSERT_MOVIMENTACOES(false, 'Produto teste', 1, 3, 5, 7, 20.55, 4);

So that now the procedure inserts the records of plots 6 and 7 automatically and also updates the invoice month to 5 and 6 respectively.

Browser other questions tagged sql postgresql function trigger sql-insert

You are not signed in. Login or sign up in order to post.