Triggers são blocos de código armazenados no banco de dados que executam uma ação implicitamente sempre que uma tabela é modificada.
Em muitos SGBD's as triggers não são similares as funções, elas são na verdade funções, diferindo apenas na maneira como são chamadas. A trigger é executada implicitamente quando ocorre algum evento de trigger enquanto uma função “convencional” deve ser executada por uma chamada explicita à mesma. O PostgreSQL difere nesse ponto por separar explicitamente o objeto trigger da função a ser executada. Isso significa que temos que criar uma função especial chamada “função de trigger” e depois criarmos a trigger propriamente dita.
Para facilitar nossa compreensão sobre as triggers vou utilizar o modelo proposto pelo professor Milton Goya e dividir em 4 partes:
- Momento
- Evento
- Tipo
- Função
O momento indica quando uma trigger irá ser acionada. Pode ser:
- BEFORE (Antes)
- AFTER (Depois)
BEFORE indica que os comandos da função serão executados ANTES dos dados da tabela serem alterados. Normalmente usamos BEFORE nos casos em que precisamos incializar variáveis globais, validar regras de negócios, alterar o valor de flags ou para salvar o valor de uma coluna antes de alterarmos o valor delas.
AFTER indica que os comandos da função serão executado APÓS os dados da tabela serem alterados. Normalmente usamos AFTER para completar os dados de outras tabelas e para completar a atividade de outra trigger.
O evento define qual é a instrução SQL que aciona a trigger, podendo ser:
- INSERT
- UPDATE
- DELETE
Uma trigger pode ser configurada para iniciar a execução pela chamada de um, dois ou pelos três eventos.
O tipo define quantas vezes uma trigger será executa:
- STATEMENT
- ROW
Quando a trigger for do tipo STATEMENT (instrução) ela será disparada uma vez para cada evento de trigger, mesmo que nenhuma linha tenha sido afetada. Logo, a cada chamada de evento (INSERT/UPDATE/DELETE) a trigger será executada uma única vez.
Quando a trigger for do tipo ROW (linha), a trigger será executada toda vez que a tabela for afetada pelo evento da trigger. Se nenhuma linha for afetada a trigger não será executada. Se em uma única instrução forem afetadas diversas linhas, a trigger será executada diversas vezes, uma execução para cada linha afetada. São muito úteis quando a ação da trigger depende dos dados afetados pelo evento da trigger.
A função, como o próprio nome diz, é uma função escrita em uma das linguagens suportadas pelo PostgreSQL, como C/C++, Pl/Pgsql, Java, PHP, e que retornam um objeto do tipo TRIGGER. Ela define de fato o que será executado quando uma trigger é invocada.
Como, normalmente, precisamos trabalhar com os valores antes e depois da alteração dos dados, a trigger permite que façamos referencia aos valores antes da alteração (OLD) e após a alteração (NEW).
Neste artigo utilizarei as linguagens “C”, SQL e Pl/Pgsql para escrever nossa função de trigger e suas auxiliares. Para o exemplo ficar interessante, vou criar uma rotina de Log, onde as alterações realizadas em uma determinada tabela sejam logadas em um arquivo no disco.
xPgLog
Esta rotina deve ser capaz de:
• Gravar em um arquivo todas as alterações (inserção, alteração, exclusão) realizadas na tabela ouvinte.
• Prover uma forma simples para registrar e remover as tabelas ouvintes, ou seja, se for necessário adicionar mais uma tabela a ser logada ou remover uma tabela do processo de log deve ser feito de uma forma simples.
• Como podem ser adicionadas novas tabelas ao processo de log, o mesmo deve ser “inteligente” o suficiente para identificar os campos das tabelas e gravar as informações de todos os campos da tabela.
• Para cada operação teremos um tipo específico de registro:
– Inserção: Terá apenas um registro contendo as informações da linha que foi inserida.
– Exclusão: Terá apenas um registro contendo as informações da linha que foi removida.
– Alteração: Terá dois registros, um contendo as informações antigas e outro contendo as novas informações da linha que foi alterada.
Bem, definido como a rotina deve proceder, vamos ao código.
Para isolar o nosso exemplo dentro do banco, vou criar um schema chamado “controle”, onde ficarão os objetos de banco que formos criar.
/*
* Criação do schema controle
*/
CREATE SCHEMA controle;
/*
* Tabela tbl_xpglog
*/
CREATE TABLE control.tbl_xpglog (
no_schema character varying(100) NOT NULL,
no_table character varying(100) NOT NULL,
CONSTRAINT pk_xpglog PRIMARY KEY (no_schema, no_table)
);
A tabela tbl_xpglog será responsável por gerenciar quem vai fazer parte do processo de log. A idéia é simples, quem estiver cadastrado na tabela tbl_xpglog fará parte do processo de log. Para automatizar esse processo vou criar duas rotinas, uma para “registrar o ouvinte”, isso é, para que quando cadastrarmos uma tabela ela seja automaticamente colocada no processo de log, e outra para “remover o ouvinte”, ou seja, remover a tabela do processo de log quando ela for removida da tbl_xpglog.
/*
* Função de trigger fc_xpglog_start
* Esta função é responsável por registrar o ouvinte para incluir uma tabela no processo de log.
*/
CREATE OR REPLACE FUNCTION control.fc_xpglog_start() RETURNS trigger AS $$
BEGIN
EXECUTE 'CREATE TRIGGER tg_xpglog
BEFORE INSERT OR UPDATE OR DELETE
ON '|| NEW.no_schema || '.' || NEW.no_table ||'
FOR EACH ROW
EXECUTE PROCEDURE control.xpglog() ';
RETURN NEW;
END; $$ LANGUAGE 'plpgsql';
/*
* Função de trigger fc_xpglog_stop
* Esta função é responsável por retirar o ouvinte para remover uma tabela do processo de log.
*/
CREATE OR REPLACE FUNCTION control.fc_xpglog_stop() RETURNS trigger AS $$
BEGIN
/*
* Este IF garante que as linhas não sejam alteradas, mas apenas excluídas
* e inseridas. Para podermos permitir alterações teríamos que implementar
* um mecanismo capaz de alterar também os registros de trigger nas
* tabelas inseridas. Trabalhar com inclusão/exclusão fica mais simples.
*/
IF TG_OP = 'UPDATE' THEN
RAISE EXCEPTION 'UPDATE NOT PERMITED.';
ELSE
EXECUTE 'DROP TRIGGER tg_xpglog ON '|| OLD.no_schema || '.' || OLD.no_table;
RETURN OLD;
END IF;
END; $$ LANGUAGE 'plpgsql';
Tendo criado as funções de trigger, precisamos criar as triggers propriamente ditas.
/*
* Trigger tg_xpglog_start
* Esta trigger garante que toda vez que uma linha for inserida em tbl_xpglog a função control.fc_xpglog_start() será executada.
*/
CREATE TRIGGER tg_xpglog_start
BEFORE INSERT
ON control.tbl_xpglog
FOR EACH ROW
EXECUTE PROCEDURE control.fc_xpglog_start();
/*
* Trigger tg_xpglog_stop
* Esta trigger garante que toda vez que uma linha for removida ou alterada em tbl_xpglog a função control.fc_xpglog_stop() será executada.
*/
CREATE TRIGGER tg_xpglog_stop
BEFORE DELETE OR UPDATE
ON control.tbl_xpglog
FOR EACH ROW
EXECUTE PROCEDURE control.fc_xpglog_stop();
Agora que criamos o mecanismo para gerenciar o registro de tabelas no processo de log, precisamos criar a função de log. Uma vez que vamos armazenar o log em um arquivo faremos a rotina em “C”.
/*
* xpglog.c
*
* Author: Charly Frankl
*
* Copyright (c) 2010 XPORT TECNOLOGIA. All rights reserved.
* This software is licensed by GPL.
*
* Toda vez que uma operação de atualização é iniciada o PostgreSQL executa uma série de eventos antes de alterar de fato os registros na tabela. Dentre
* os eventos que podem ser disparados temos as triggers.
* Para podermos trabalhar com as triggers temos a nossa disposição diversos objetos na lib do PostgreSQL, dos quais destaco:
*
* - TriggerData
* A struct TriggerData definida em commands/trigger.h:
* Quando uma função de trigger é chamada não é passado nenhum argumento normal, envez disso é passado um ponteiro de "contexto" apontando para a estrutura TriggerData.
* A struct TriggerData contém as informações a respeito da operação que está sendo realizada, como informações da tabela, evento que disparou a trigger, a tupla (linha)
* que está sendo atualizada com seus novos e antigos valores, quando existirem, dentre outras informações. Ela é definida por:
*
* typedef struct TriggerData
* {
* NodeTag type;
* TriggerEvent tg_event;
* Relation tg_relation;
* HeapTuple tg_trigtuple;
* HeapTuple tg_newtuple;
* Trigger *tg_trigger;
* Buffer tg_trigtuplebuf;
* Buffer tg_newtuplebuf;
* } TriggerData;
*
*
* - Macros
* O PostgreSQL disponibiliza algumas macros que estão relacionadas a triggers, e abaixo listo algumas que estaremos utilizando:
* * CALLED_AS_TRIGGER(fcinfo)
* Esta macro permite as funções em C verificar se foram chamadas pelo gerenciador de gatilhos.
*
* * TRIGGER_FIRED_BEFORE(tg_event)
* Retorna verdade se o gatilho disparou antes da operação.
*
* * TRIGGER_FIRED_AFTER(tg_event)
* Retorna verdade se o gatilho disparou depois da operação.
*
* * TRIGGER_FIRED_FOR_ROW(tg_event)
* Retorna verdade se o gatilho disparou para um evento no nível-de-linha.
*
* * TRIGGER_FIRED_FOR_STATEMENT(tg_event)
* Retorna verdade se o gatilho disparou para um evento no nível-de-instrução.
*
* * TRIGGER_FIRED_BY_INSERT(tg_event)
* Retorna verdade se o gatilho foi disparado por um comando INSERT.
*
* * TRIGGER_FIRED_BY_UPDATE(tg_event)
* Retorna verdade se o gatilho foi disparado por um comando UPDATE.
*
* * TRIGGER_FIRED_BY_DELETE(tg_event)
* Retorna verdade se o gatilho foi disparado por um comando DELETE.
*
* - Além da struct TriggerData é importante conhecermos algumas outras, que são:
* * Relation (utilizada dentro de TriggerData como o atributo tg_relation)
* Representa uma tabela. No nosso caso a tabela para a qual o gatilho foi disparado. Os itens mais interessantes são:
* > rd_att (descritor das linhas da tabela).
* > rd_rel->relname (nome da tabela. Este atributo não é um ponteiro para char, e sim um NameData. Para que possamos recuperar o nome
* da tabela como um char* devemos utilizar a função SPI_getrelname(tg_relation).
*
* * HeapTuple
* É uma estrutura que representa uma linha (tupla). No nosso caso ela é utilizada em dois atributos dentro de TriggerData, e são eles:
* tg_trigtuple: Ponteiro para a linha para a qual o gatilho foi disparado. Temos os seguintes comportamentos:
* > INSERT: Esta é a linha que está sendo inserida.
* > DELETE: Esta é a linha que está sendo removida.
* > UPDATE: Esta é a linha que está sendo alterada, ou seja, a linha antiga.
* tg_newtuple: Ponteiro para a nova versão da linha, se o gatilho foi disparado por um UPDATE, ou NULL, se foi disparado por um INSERT ou DELETE.
*
* - Server Programming Interface
* O PostgreSQL disponibiliza uma API para trabalharmos com o servidor, a qual é chamada de SPI. Aqui estaremos utilizando a mesma para executarmos
* algumas instruções dentro do banco. A função mencionada acima SPI_getrelname(tg_relation) é um exemplo de função disponibilizada pela SPI. Para
* maiores detalhes, vide manual do PostgreSQL.
*
*
*
* *** IMPORTANTE ***
* Uma função de gatilho deve retornar um ponteiro para HeapTuple ou um ponteiro NULL (não um valor SQL nulo). Para as operações de INSERT e DELETE deve
* retornar TriggerData->tg_trigtuple para confirmar a operação. No caso de um UPDATE TriggerData->tg_newtuple dever ser retornado. Se alteramos os valores
* de uma dessas estruturas em nossa função, esses serão os valores gravados no banco de dados.
*
*/
#include "xpglog.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
extern Datum xpglog(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(xpglog);
Datum xpglog(PG_FUNCTION_ARGS)
{
TriggerData *triggerData = (TriggerData *) fcinfo->context;
TupleDesc tupleDesc;
HeapTuple heapTuple;
int ret;
char evento[7];
/* certificar-se que foi chamado como um trigger */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "xpglog: não foi chamada por um gerenciador de gatilho");
/* Verifica qual evento disparou o trigger, identificando assim a tupla a ser retornada para o executor */
if ( TRIGGER_FIRED_BY_UPDATE(triggerData->tg_event) )
{
heapTuple = triggerData->tg_newtuple;
strcpy(evento, "UPDATE");
}
else{
heapTuple = triggerData->tg_trigtuple;
if (TRIGGER_FIRED_BY_INSERT(triggerData->tg_event))
strcpy(evento, "INSERT");
if (TRIGGER_FIRED_BY_DELETE(triggerData->tg_event))
strcpy(evento, "DELETE");
}
tupleDesc = triggerData->tg_relation->rd_att;
/* Procedimento para nos conectarmos ao gerenciador de SPI */
if ( (ret = SPI_connect()) < 0 ){
elog(ERROR, "xpglog: SPI_connect returned %d", ret);
return;
}
/*
* Verifica qual parametro devemos passar para a inclusão do registro de log.
* No caso de um udate devemos passar tanto a linha antiga quanto a linha nova.
* Para as outras operações passamos apenas a linha "nova" e a linha antiga fica NULL.
*/
if ( TRIGGER_FIRED_BY_UPDATE(triggerData->tg_event) )
XLOG(tupleDesc, heapTuple, triggerData->tg_trigtuple, triggerData->tg_relation, evento);
else
XLOG(tupleDesc, heapTuple, NULL, triggerData->tg_relation, evento);
/* Procedimento para nos desconectarmos do gerenciador de SPI */
SPI_finish();
/*
* Retorna a tupla para o executor. Lembrando que nos casos de INSERT/DELETE retornamos TriggerData->tg_trigtuple
* e para o update TriggerData->tg_newtuple.
*/
return PointerGetDatum(heapTuple);
}
/*
* xlogFile.c
*
* Author: Charly Frankl
*
*/
#include "xpglog.h"
/*
* bool xlogFile(TupleDesc tupleDesc, HeapTuple heapTupleNew, HeapTuple heapTupleOld, Relation relation, char *evento)
* Grava as alterações da tabela no arquivo de log.
*
*/
bool xlogFile(TupleDesc tupleDesc, HeapTuple heapTupleNew, HeapTuple heapTupleOld, Relation relation, char *evento)
{
/* Ponteiro para o arquivo de log */
FILE *logFile;
/* Estrutura para trabalharmos com data */
time_t currtime;
/*
* Variaveis para armazenar os valores de data.
* charTime : Armazena data e hora para ser gravada no log
* charTimeFile : Armazena data para compor o nome do arquivo de log. Deverá ser criado um arquivo de log por dia para diminuir o custo de escrita no arquivo.
*/
char charTime[20] = {0};
char charTimeFile[10] = {0};
/* Identifica data e hora atual */
time(&currtime);
strftime(charTime, sizeof(charTime)+10, "%Y-%m-%d %H:%M:%S", localtime(&currtime));
strftime(charTimeFile, sizeof(charTime)-1, "%Y%m%d", localtime(&currtime));
/* Define o caminho completo do arquivo a ser aberto */
char *filePath = NULL;
filePath = (char *) palloc( (strlen(LOGFILE_PATH) + strlen(charTimeFile) + 10) * sizeof(char) );
sprintf(filePath, "%s/%s.log", LOGFILE_PATH, charTimeFile);
/* Abre arquivo para o log */
logFile = AllocateFile(filePath, "a+");
pfree(filePath);
/*
* Grava a linha de "header" do registro de log, contendo a estrutura:
* data_hora:evento:nome_do_schema.nome_da_tabela
*
* Cada registro de log conterá esta linha, e depois o log de alteração propriamente dito.
*/
fprintf(logFile, "\n%s:%s:%s.%s\n\t",
charTime,
evento,
SPI_getnspname(relation), SPI_getrelname(relation));
/*
* Verifica se é uma instrucao de UPDATE.
* Se for, grava a linha antiga primeiro e depois grava a linha nova.
* Caso não seja uma operação de UPDATE grava apenas a linha "nova".
*/
if (heapTupleOld != NULL)
{
_xlogFileLine(logFile, tupleDesc, heapTupleOld);
fprintf(logFile, "\n\t");
}
/* Grava a linha nova no log. */
_xlogFileLine(logFile, tupleDesc, heapTupleNew);
/*
* Libera (fecha) o arquivo alocado para o log.
* Caso não consiga fechar o arquivo retorna uma mensagem de erro e aborta a operação.
*/
if (FreeFile(logFile))
{
ereport(LOG,
(errcode_for_file_access(),
errmsg("could not write archive status file \"%s\": %m",
"/opt/postgres/teste.txt")));
return false;
}
return true;
}
/*
* _xlogFileLine(FILE *logFile, TupleDesc tupleDesc, HeapTuple heapTuple)
* Grava cada linha de alteração da tabela no arquivo de log. Utilizado pela função xlogFile.
*
*/
void _xlogFileLine(FILE *logFile, TupleDesc tupleDesc, HeapTuple heapTuple)
{
int i;
/*
* Varre a linha para pegar todos os registros que a mesma contém e gravar no arquivo de log.
* Utiliza o inteiro TupleDesc->natts para recuperar a quantidade de atributos (campos) presentes
* na linha passada como parâmetro.
*/
for(i = 1; i <= tupleDesc->natts; i++){
if(i > 1)
fprintf(logFile, ",");
/*
* Verifica se é um atributo numérico.
*/
switch (TypeCategory(SPI_gettypeid(tupleDesc, i))){
case NUMERIC_TYPE:
/* Caso seja um atributo numérico grava o valor do campo como está. */
fprintf(logFile, "%s", SPI_getvalue(heapTuple, tupleDesc, i));
break;
default:
/* Caso não seja um atributo numérico grava o valor do campo entre aspas simples. */
fprintf(logFile, "'%s'", SPI_getvalue(heapTuple, tupleDesc, i));
}
}
}
/*
* xpglog.h
*
* Author: Charly Frankl
*
*/
#ifndef XPGLOG_H_
#define XPGLOG_H_
#include "postgres.h"
#include "executor/spi.h" /* necessário para trabalhar com SPI */
#include "commands/trigger.h" /* ... e gatilhos */
#include "parser/parse_coerce.h" /* ... e para funções e constantes de definições de tipos (ex. NUMERIC_TYPE) */
#include <time.h>
#include <stdlib.h>
#include <stdio.h>
#define DELIMITER_LEN 4
#define SQL_LEN 100
#define LOGFILE_PATH "/opt/postgres"
bool xlogFile(TupleDesc, HeapTuple, HeapTuple, Relation, char *);
void _xlogFileLine(FILE *, TupleDesc, HeapTuple);
bool xlogDB(TupleDesc, HeapTuple, HeapTuple, Relation, char *);
char * xlogDBLine(TupleDesc, HeapTuple);
/*
* Cria uma macro encapsulando a função de gravação do log.
* Foi criada para quando forem implementadas funções para gravar logs no DB ou com outra
* estrutura que não seja a definida aqui basta alterar a macro.
*/
#define XLOG(tupleDesc, heapTupleNew, heapTupleOld, relation, operation ) xlogFile(tupleDesc, heapTupleNew, heapTupleOld, relation, operation )
#endif /* XPGLOG_H_ */
Com as funções criadas, basta apenas compilarmos os códigos e testarmos nosso processo de log. Para compilarmos os arquivos utilizando o GCC devemos primeiro descobrir onde ficam os arquivos de cabeçalho (arquivos de include) do PostgreSQL. No meu caso eles estão no diretório “/usr/local/pgsql/include”. Tendo descoberto onde estão, basta compilarmos com as instruções:
gcc -fpic -o xlogFile.o -c xlogFile.c -I/usr/local/pgsql/include -I/usr/local/pgsql/include/server
gcc -fpic -o xpglog.o -c xpglog.c -I/usr/local/pgsql/include -I/usr/local/pgsql/include/server
Depois, criamos a biblioteca dinâmica para o PostgreSQL:
gcc -shared -o xpglog.so xpglog.o xlogFile.o
A instrução acima cria um arquivo chamado xpglog.so. Devemos copiar esse arquivo para a pasta de bibliotecas do PostgreSQL, ou então para uma pasta onde o banco tenha privilégios de leitura/escrita. Para descobrirmos onde está a pasta de bibliotecas dinâmicas do banco basta executarmos a instrução no terminal do SO:
pg_config --libdir
Depois de copiarmos a biblioteca para a pasta apropriada devemos “importar” a função dentro do PostgreSQL, criando a função de trigger a partir de nossa função em “C”:
/*
* Função de trigger xpglog
*/
CREATE OR REPLACE FUNCTION control.xpglog() RETURNS trigger AS 'xpglog.so' LANGUAGE C;
É importante ressaltar que ela deve ter o mesmo nome da função que criamos dentro da nossa biblioteca desenvolvida em “C”.
Com isso finalizamos nossa rotina de log.
Nenhum comentário:
Postar um comentário