How to create Trigger postgre

Question

In relation to the table above,

1) Create a Rigger that for each record inserted in tb_move, the account balance is updated in tb_balance. Remembering that if the launch is of the type C' increases the balance, if it is of the 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_move record, the balance is automatically updated in tb_balance;

Can anyone help me? haha

Answer 1

Creating Structure of Tables:

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

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

Creation of 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 - Recovers the Balance of all registered Customers before of Movements:

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

Exit:

Test - Simulate Customer Movements "JOAO":

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 Customer’s Movements "MARIA":

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 - Recovers the Balance of all registered Customers afterward of Movements:

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

Exit: