Cursor Trigger in book allocation

Question

Cursor Trigger in book allocation

Navigation

#1 by (0 votes)

-1

Well, I have a table called LOCALAOBOOK in it I have BOOK and CUSTOMER information when an insert or update of allocation occurs, so the customer_id, id_id, the output_data will be informed and from this date the delivery_date_date will be generated, which will add 7 days to the date I created a table named alocacao_livro, a trigger and a cursor follow the codes below.From already myth grate!

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

postgresql trigger cursor plpgsql

asked by anonymous 29.05.2016 / 20:51

1 answer

Encrypt passwords Relative Path in VBScript

score 0 · Accepted Answer

If your goal is to only add the date of return forecast, you do not have to use a cursor, you just have to trigger your trigger before inserting, because in the update you will not do this because the date has already been added in insert and in the procedure add the following date:

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;

You can still do without using that variable, it would look 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 chose this type of implementation, but if you do it this way it would still validate if the loan date is different from the current one and shoot the exception of the bank, since the loan, I assume, 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;