Início / dba / Perguntas / 30069
Accepted
Dave Jarvis
Asked: 2012-12-10 14:18:13 +0800 CST

Herdar colunas e gatilhos de auditoria

  • 772

Fundo

Tenho um banco de dados com dezenas de tabelas, algumas das quais deveriam ter colunas de auditoria.

Problema

Gostaria de evitar o trabalho penoso de criar colunas de auditoria manualmente e escrever os gatilhos updatee uma vez.delete

Código

Existem vários pedaços diferentes de código que devem vir juntos.

Tabela de Auditoria

O código a seguir cria uma tabela de auditoria e uma tabela de cidade:

CREATE TABLE audit (
  created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone,
  deleted timestamp with time zone,
  updated timestamp with time zone
);

Tabela de Exemplo

Uma tabela simples para servir de exemplo do problema:

CREATE TABLE city (
  name       text,
  population real,
  altitude   int
);

Acionador da tabela pai

O código a seguir cria um gatilho de banco de dados e uma regra de prevenção de exclusão na tabela de auditoria:

CREATE FUNCTION audit_delete() RETURNS trigger AS
$audit_delete$
BEGIN
  OLD.deleted := current_timestamp;
  RETURN NULL;
END;
$audit_delete$
LANGUAGE plpgsql;

CREATE TRIGGER audit_delete BEFORE DELETE ON audit
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

Adicionando colunas

A herança requer que as colunas na tabela filho correspondam à tabela pai e devem ser criadas manualmente. Tal como:

for $name in (SELECT list of tables); do
  ALTER TABLE $name ADD COLUMN
    created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    deleted timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    updated timestamp with time zone;
done

Provavelmente existe uma maneira de detectar e adicionar automaticamente as colunas ausentes usando o esquema de informações.

Estabelecer Hierarquia de Herança

O código a seguir aplica a herança à tabela fornecida:

ALTER TABLE city INHERIT audit;

Inserir Dados

Adicione alguns dados no banco de dados:

INSERT INTO city ('Vancouver', 603502, 10 );
INSERT INTO city ('Seattle', 620887, 10 );
INSERT INTO city ('La Rinconada', 30000, 5100 );
INSERT INTO city ('Jericho', 20300, -260 );

Excluir dados

Remova alguns dados:

DELETE FROM city WHERE name = 'Jericho';

Nesse ponto, eu esperava que a deletedcoluna contivesse a data para o valor de "Jericho". Em vez disso, a linha foi realmente removida do banco de dados.

O gatilho para audit_delete()não disparou.

Perguntas

  1. Qual é a maneira ideal (ou usual) de criar um gatilho que define a data de exclusão em deleteconsultas para todas as tabelas herdadas da tabela de auditoria (usando o PostgreSQL 9.2)?
  2. (Opcional) Esse é um bom uso para gatilhos?

Obrigada!

Links Relacionados

postgresql trigger

1 respostas

  1. Best Answer

    De http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html :

    Os gatilhos DELETE e UPDATE precisam estar nas tabelas filhas. Uma operação em um filho não dispara os gatilhos do pai.

    Como tal, o seguinte código também deve ser executado:

    CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

    A audit_delete()função não funcionará conforme escrito na pergunta: não é possível definir a data excluída usando OLD.deleted = current_timestamp. Em vez disso, supondo que a tabela tenha um campo de chave primária de id:

    CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;
    • 2

relate perguntas