Banco de Dados II - Triggers - Estrutura das tabelas, função e gatilho

bd2_triggers_02_estrutura.sql

--
-- Banco de Dados 2
-- Gatilhos
--
-- Estrutura das tabelas, função e gatilho
--
-- Referências:
-- * DINIZ, Eduardo. "Triggers (Gatilhos)".
-- * http://www.postgresql.org/docs/8.4/static/plpgsql.html
-- * http://www.postgresql.org/docs/8.4/static/plpgsql-structure.html
-- * http://www.postgresql.org/docs/8.4/static/trigger-example.html
--

COMMENT ON DATABASE bd2 IS 'Banco de Dados II 2/2011';

-- PL/pgSQL é requerida para função gerar_log()
CREATE LANGUAGE plpgsql;


--
-- Remove as tabelas e sequências
--
DROP TABLE IF EXISTS usuarios;
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS acoes;

DROP SEQUENCE IF EXISTS usuarios_id_seq;
DROP SEQUENCE IF EXISTS logs_id_seq;


--
-- Usuários
--
CREATE SEQUENCE usuarios_id_seq START 1;

CREATE TABLE usuarios (
  id integer NOT NULL DEFAULT NEXTVAL('usuarios_id_seq'),
  usuario varchar(20) NOT NULL,
  senha varchar(20) NOT NULL,
  nome varchar(50) NOT NULL,
  email varchar(100) DEFAULT NULL,
  horaCadastro timestamp DEFAULT LOCALTIMESTAMP(0),
  PRIMARY KEY (id),
  UNIQUE (usuario)
);


--
-- Ações
--
CREATE TABLE acoes (
  id integer NOT NULL,
  descricao varchar(40) NOT NULL,
  PRIMARY KEY (id)
);


--
-- Tabela de Logs
--
CREATE SEQUENCE logs_id_seq START 1;

CREATE TABLE logs (
  id integer NOT NULL DEFAULT NEXTVAL('logs_id_seq'),
  idAcao integer DEFAULT NULL,
  operador varchar(25) DEFAULT CURRENT_USER,
  ipOperador inet DEFAULT INET_CLIENT_ADDR(),
  dataHora timestamp DEFAULT LOCALTIMESTAMP(0),
  PRIMARY KEY (id),
  FOREIGN KEY (idAcao) REFERENCES acoes (id) ON DELETE SET NULL ON UPDATE CASCADE
);


--
-- Função de Geração de Log
--
CREATE OR REPLACE FUNCTION gerar_log() RETURNS trigger AS $$
  DECLARE
    idAc integer;
    acao varchar := TG_OP; /* Tipo da operação sendo executada */
  
  BEGIN
    IF acao = 'INSERT' THEN
      idAc := 1;
    ELSE
      IF acao = 'UPDATE' THEN
        idAc := 2;
      ELSE
        idAc := 3;
      END IF;
    END IF;
    
    INSERT INTO logs (idAcao) VALUES (idAc);
    
    RETURN NEW;
  END;
$$ LANGUAGE 'plpgsql';


--
-- Trigger (gatilho)
--
-- Dispara *após* inserção OU atualização OU remoção
-- na tabela de usuários.
--
CREATE TRIGGER tr_gera_log
  AFTER INSERT OR UPDATE OR DELETE ON usuarios
  FOR EACH ROW EXECUTE PROCEDURE gerar_log();





-- Fim.