Triggers en Postgresql

ejemplo_auditoria.sql

DROP TABLE IF EXISTS emp CASCADE;
DROP TABLE IF EXISTS emp_audit;
DROP FUNCTION IF EXISTS update_emp_view();
DROP FUNCTION IF EXISTS process_emp_audit();

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

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

INSERT INTO emp VALUES ('Juan',100);
INSERT INTO emp VALUES ('Marcos',110);

SELECT * FROM emp_audit;

UPDATE emp SET salary = salary * 1.2;

SELECT * FROM emp_audit;

DELETE FROM emp;

SELECT * FROM emp_audit;