Trigger cursor in book allocation

Question

Well, I have a table called LOCACAO_LIVRO in it I have BOOK and CLIENT information when an allocation update occurs then the id_client will be informed, id_book, the data_de_output and from this date will be generated the data_de_previssao_delivery that will add 7 days to the date of departure, for this I created a table called alocaca_book, a Trigger and a cursor follow the codes below.Already grateful myth!

-- TABELA LIVRO
CREATE TABLE LIVRO(
ID_LIVRO SERIAL NOT NULL PRIMARY KEY,
ISBN INTEGER NOT NULL UNIQUE,
NOME VARCHAR(255) NOT NULL,
AUTOR VARCHAR(255) NOT NULL,
EDITORA VARCHAR(255) NOT NULL,
ANO_PUBLICACAO INTEGER NOT NULL);

-- TABELA CLIENTE
CREATE TABLE CLIENTE(
ID_CLIENTE SERIAL NOT NULL PRIMARY KEY,
MATRICULA INTEGER NOT NULL UNIQUE,
NOME VARCHAR(255) NOT NULL,
TELEFONE VARCHAR(15) NOT NULL);

-- TABELA ALOCAÇÃO_LIVRO
CREATE TABLE LOCACAO_LIVRO(
ID_LOCACAO SERIAL NOT NULL PRIMARY KEY,
ID_CLIENTE INTEGER NOT NULL REFERENCES CLIENTE,
ID_LIVRO INTEGER NOT NULL REFERENCES LIVRO,
DT_SAIDA DATE,
DT_PREVISAO_ENTREGA DATE,
DT_ENTREGA DATE);

MY TRIGGER

CREATE TRIGGER TR_AF_CONTROLA_DT_PREVISAO_ENTREGA_LIVRO_TB_LOCACAO_LIVRO
AFTER INSERT OR UPDATE ON LOCACAO_LIVRO
FOR EACH ROW
EXECUTE PROCEDURE FN_GERAR_DT_PREVISAO_ENTREGA_LIVRO();

MY FUNCTION (CURSOR)

CREATE OR REPLACE FUNCTION FN_GERAR_DT_PREVISAO_ENTREGA_LIVRO()
RETURNS TRIGGER AS
$$
DECLARE
    VAR_ID_LOCACAO INTEGER;
    VAR_ID_CLIENTE INTEGER;
    VAR_ID_LIVRO INTEGER;
    VAR_DT_SAIDA DATE;
    VAR_DT_PREVISAO_ENTREGA DATE;
    CURSOR_INSERE_DT_PREVISTA_ENTREGA CURSOR FOR SELECT
    ID_LOCACAO, ID_CLIENTE, ID_LIVRO, DT_SAIDA, DT_PREVISAO_ENTREGA
    FROM LOCACAO_LIVRO
    WHERE ID_LOCACAO IS NOT NULL;
BEGIN
    OPEN CURSOR_INSERE_DT_PREVISTA_ENTREGA;
    FETCH CURSOR_INSERE_DT_PREVISTA_ENTREGA INTO VAR_ID_LOCACAO, VAR_ID_CLIENTE, VAR_ID_LIVRO, VAR_DT_SAIDA, VAR_DT_PREVISAO_ENTREGA;
    WHILE FOUND LOOP
        VAR_DT_PREVISAO_ENTREGA = VAR_DT_SAIDA + 7;

        IF(VAR_ID_LOCACAO IS NOT NULL)THEN
            UPDATE LOCACAO_LIVRO
                SET DT_SAIDA = VAR_DT_SAIDA, DT_PREVISAO_ENTREGA = VAR_DT_PREVISAO_ENTREGA
                WHERE ID_LOCACAO = VAR_ID_LOCACAO
                AND ID_CLIENTE = VAR_ID_CLIENTE
                AND ID_LIVRO = VAR_ID_LIVRO;
        END IF;
        FETCH CURSOR_INSERE_DT_PREVISTA_ENTREGA INTO VAR_ID_LOCACAO, VAR_ID_CLIENTE, VAR_ID_LIVRO, VAR_DT_SAIDA, VAR_DT_PREVISAO_ENTREGA;
        END LOOP;
        CLOSE CURSOR_INSERE_DT_PREVISTA_ENTREGA;
        RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;

Answer 1

If your goal is only to add the return prediction date, you don’t have to use a cursor, just make your Rigger be triggered before the Insert, because in the update you won’t do it because the date has already been added in the Insert and in the process add the date follows:

CREATE TRIGGER TR_AF_CONTROLA_DT_PREVISAO_ENTREGA_LIVRO_TB_LOCACAO_LIVRO
BEFORE INSERT ON LOCACAO_LIVRO
FOR EACH ROW
EXECUTE PROCEDURE FN_GERAR_DT_PREVISAO_ENTREGA_LIVRO();


CREATE OR REPLACE FUNCTION FN_GERAR_DT_PREVISAO_ENTREGA_LIVRO()
RETURNS TRIGGER AS
$$
DECLARE
    VAR_DT_PREVISAO_ENTREGA DATE;
BEGIN
    IF (TG_OP = 'INSERT')  THEN //não precisa desse if, mas se for depois do update isso é necessário
        VAR_DT_PREVISAO_ENTREGA := new.DT_SAIDA + 7;      
        new.DT_PREVISAO_ENTREGA := VAR_DT_PREVISAO_ENTREGA;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;

Can still do without the use of that variable, would be like this:

 CREATE OR REPLACE FUNCTION FN_GERAR_DT_PREVISAO_ENTREGA_LIVRO()
 RETURNS TRIGGER AS
 $$
 BEGIN
    IF (TG_OP = 'INSERT')  THEN
        new.DT_PREVISAO_ENTREGA := new.DT_SAIDA + 7;   
    END IF;
    RETURN NEW;
 END;
 $$
 LANGUAGE PLPGSQL;

But this can be done more easily directly in the application, but I do not know the reasons why you opted for this type of implementation, but if you do so it would still be valid if the loan date is different from the current one and trigger the bank exception, since the loan, I presume, must be registered on the day of the lease, follows:

CREATE OR REPLACE FUNCTION FN_GERAR_DT_PREVISAO_ENTREGA_LIVRO()
RETURNS TRIGGER AS
$$
BEGIN
    IF (NEW.DT_SAIDA <> CURRENT_DATE) THEN
        RAISE EXCEPTION 'Data de saída --> % é diferente da data atual',NEW.DT_SAIDA
        USING HINT = 'Data de saida deve ser igual a data atual';
    ELSIF (TG_OP = 'INSERT')  THEN
        new.DT_PREVISAO_ENTREGA := new.DT_SAIDA + 7;   
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;