How to create postgre trigger

Question

How to create postgre trigger

Navigation

#1 by (1 votes)

-3

In relation to the table above,

1) Create a trigger that for each record inserted in tb_movimentacao, the balance of the account is updated in tb_saldo. Remember that if the release is of type 'C' increases the balance, if it is of type 'D' decreases the balance - (procedure valid for all triggers to be created) 2) Create a trigger that with each change in the Value field in the tb_movement register, the balance is automatically updated in tb_saldo;

Can anyone help me? haha

postgresql

asked by anonymous 05.07.2017 / 02:11

1 answer

Convert number to minute and subtract these minutes from a column containing date and time R [duplicate] Parse error: syntax error, unexpected '}' in C: \ xampp \ htdocs \ social-networking \ index.php on line 15 [closed]

score 1 · Accepted Answer

Creating Table Structure:

-- CLIENTE
CREATE TABLE tb_cliente
(
    id BIGINT,
    nome TEXT
);

-- MOVIMENTACAO
CREATE TABLE tb_movimentacao
(
    id BIGINT,
    id_cliente BIGINT,
    tipo VARCHAR(1),
    valor REAL
);

-- MOVIMENTACAO
CREATE TABLE tb_saldo
(

    id_cliente BIGINT,
    valor REAL
);

Creating Trigger Function :

CREATE OR REPLACE FUNCTION fc_atualizar_saldo()
  RETURNS trigger AS
$BODY$
DECLARE
    n REAL DEFAULT 0;
BEGIN
    IF( NEW.tipo = 'D' ) THEN
        n = NEW.valor * (-1);
    ELSIF( NEW.tipo = 'C' ) THEN 
        n = NEW.valor;
    END IF;

    IF NOT EXISTS( SELECT 1 FROM tb_saldo WHERE id_cliente = NEW.id_cliente ) THEN
        INSERT INTO tb_saldo ( id_cliente, valor ) VALUES (  NEW.id_cliente, n );
    ELSE
        UPDATE tb_saldo SET valor = valor + n WHERE id_cliente = NEW.id_cliente;
    END IF;

    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Creating Trigger :

CREATE TRIGGER trg_atualizar_saldo AFTER INSERT OR UPDATE ON tb_movimentacao FOR EACH ROW EXECUTE PROCEDURE fc_atualizar_saldo();

Test - Customer Registration:

INSERT INTO tb_cliente ( id, nome ) VALUES ( 1, 'JOAO' );
INSERT INTO tb_cliente ( id, nome ) VALUES ( 2, 'MARIA' );
INSERT INTO tb_cliente ( id, nome ) VALUES ( 3, 'FULANO' );

Test - Retrieve the Balance of all registered Customers before of the Transactions:

SELECT 
    c.id,
    c.nome,
    COALESCE( s.valor, 0.0 ) AS saldo
FROM
    tb_cliente c
LEFT JOIN
    tb_saldo AS s ON ( s.id_cliente = c.id );

Output:

Test-Simulate"JOAO" Client Moves:

INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 1, 1, 'C', 1000.00 ); -- Saldo: +1000.00
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 2, 1, 'D', 10.75 ); -- Saldo: +989.25
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 3, 1, 'D', 22.50 ); -- Saldo: +966.75
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 4, 1, 'C', 100.00 ); -- Saldo: +1066.75
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 5, 1, 'D', 1000.00 ); -- Saldo: +66.75

Test - Simulate "MARIA" Client Moves:

INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 1, 2, 'C', 1000.00 ); -- Saldo: +1000.00
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 2, 2, 'C', 200.00 ); -- Saldo: +1200.00
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 3, 2, 'D', 500.00 ); -- Saldo: +700.00
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 4, 2, 'D', 100.00 ); -- Saldo: +600;00
INSERT INTO tb_movimentacao ( id, id_cliente, tipo, valor ) VALUES ( 5, 2, 'D', 10.00 ); -- Saldo: +590.00

Test - Retrieves the Balance of all registered Customers after of the Transactions:

SELECT 
    c.id,
    c.nome,
    COALESCE( s.valor, 0.0 ) AS saldo
FROM
    tb_cliente c
LEFT JOIN
    tb_saldo AS s ON ( s.id_cliente = c.id );

Output: