39.4. Gatilhos escritos em PL/Ruby

Exemplo 39-13. Gatilho em PL/Ruby registrar as atualizações da linha

Neste exemplo é mostrado um gatilho escrito em PL/Ruby que conta e registra o número de atualizações das linhas de uma tabela, além de registrar o momento e o login do usuário da última atualização.

CREATE TABLE tbl_registra_modif (
    nome            TEXT,
    salario         INTEGER,
    num_modif       INTEGER,
    data_ult_modif  TIMESTAMP,
    login_ult_modif TEXT);

CREATE OR REPLACE FUNCTION fun_registra_modif() RETURNS TRIGGER AS $$
    case tg["op"]
    when PL::INSERT
        new["num_modif"] = 0
        new["data_ult_modif"] = Time.now
        new["login_ult_modif"] = (PLruby.exec("SELECT current_user"))[0]["current_user"]
    when PL::UPDATE
        new["num_modif"] = old["num_modif"] + 1
        new["data_ult_modif"] = Time.now
        new["login_ult_modif"] = (PLruby.exec("SELECT current_user"))[0]["current_user"]
    else
        return PL::OK
    end
      new
$$ LANGUAGE 'plruby';

CREATE TRIGGER gat_registra_modif BEFORE INSERT OR UPDATE ON tbl_registra_modif
    FOR EACH ROW EXECUTE PROCEDURE fun_registra_modif();

Para testar o gatilho foram utilizados os comandos:

INSERT INTO tbl_registra_modif (nome,salario) VALUES ('Joana',1500);
INSERT INTO tbl_registra_modif (nome,salario) VALUES ('Pedro',1000);
INSERT INTO tbl_registra_modif (nome,salario) VALUES ('Maria',1100);
UPDATE tbl_registra_modif SET salario = 1200 WHERE nome = 'Pedro';
UPDATE tbl_registra_modif SET salario = 1300 WHERE nome = 'Maria';
UPDATE tbl_registra_modif SET salario = 1400 WHERE nome = 'Pedro';
UPDATE tbl_registra_modif SET salario = 1500 WHERE nome = 'Maria';
UPDATE tbl_registra_modif SET salario = 1500 WHERE nome = 'Pedro';
SELECT * FROM tbl_registra_modif;

 nome  | salario | num_modif |       data_ult_modif       | login_ult_modif
-------+---------+-----------+----------------------------+-----------------
 Joana |    1500 |         0 | 2006-01-14 12:56:52.046658 | postgres
 Maria |    1500 |         2 | 2006-01-14 12:56:52.074648 | postgres
 Pedro |    1500 |         3 | 2006-01-14 12:56:52.077713 | postgres
(3 linhas)

Exemplo 39-14. Gatilho em PL/Ruby para auditoria

Este gatilho garante que todas as inserções, atualizações e exclusões de linha na tabela emp são registradas na tabela emp_audit, para permitir auditar as operações efetuadas na tabela emp. O nome de usuário e a hora corrente são gravadas na linha, junto com o tipo de operação que foi realizada.

A inserção das linhas na tabela emp_audit são feitas através da execução de um plano preparado através de PL::PLan::new, usando substituição de variáveis. Os comandos utilizados para criar as tabelas e o gatilho estão mostrados abaixo:

CREATE TABLE emp (
    nome_emp    text NOT NULL,
    salario     integer
);

CREATE TABLE emp_audit(
    operacao    char(1)   NOT NULL,
    usuario     text      NOT NULL,
    data        timestamp NOT NULL,
    nome_emp    text      NOT NULL,
    salario     integer
);

CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
    $Plans["plan"] = PL::Plan.new(
        "INSERT INTO emp_audit SELECT $1, user, now(), $2, $3",
        ["text","text","int4"]).save
    case tg["op"]
        when PL::INSERT
            n = $Plans["plan"].exec(["I",new["nome_emp"], new["salario"]], 1)
        when PL::UPDATE
            n = $Plans["plan"].exec(["A",new["nome_emp"], new["salario"]], 1)
        when PL::DELETE
            n = $Plans["plan"].exec(["E",old["nome_emp"], old["salario"]], 1)
    end
    # o resultado é ignorado uma vez que este é um gatilho AFTER
    return PL::OK
$emp_audit$ LANGUAGE 'plruby';

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

Para testar o gatilho foram utilizados os comandos:

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';
DELETE FROM emp WHERE nome_emp = 'João';

SELECT * FROM emp;

 nome_emp | salario
----------+---------
 José     |    1500
 Maria    |    2500
(2 linhas)

SELECT * FROM emp_audit;

 operacao | usuario  |            data            | nome_emp | salario
----------+----------+----------------------------+----------+---------
 I        | postgres | 2006-01-14 17:30:08.172759 | João     |    1000
 I        | postgres | 2006-01-14 17:30:08.177594 | José     |    1500
 I        | postgres | 2006-01-14 17:30:08.211178 | Maria    |     250
 A        | postgres | 2006-01-14 17:30:08.214325 | Maria    |    2500
 E        | postgres | 2006-01-14 17:30:08.217439 | João     |    1000
(5 linhas)