Trabalhando com Triggers no PostgreSQL

Neste artigo veremos na prática a utilização das triggers no banco de dados PostgreSQL 9.4, além de aprendermos a utilizar as rules.

A medida que a base de dados vai crescendo, o mesmo ocorre com a sua complexidade, e devido a isto, fica difícil implementar ou mesmo solucionar problemas que o banco deve executar antes ou depois de um evento específico.

No PostgreSQL temos a nossa disposição as rules (regras), que são tarefas que podem ser realizadas em conjunto com as triggers responsáveis pela sua execução. Podemos utilizar as rules para implementar as Views no PostgreSQL, que são tabelas virtuais que nos ajudam no agrupamento de dados presentes em uma ou mais tabelas. Neste artigo aprenderemos a trabalhar com as triggers e sua relação com as Rules e as Views.

DevCast: Você usa Triggers?

Uma visão geral sobre as triggers

Triggers, em termos de banco de dados, são as operações realizadas de forma espontânea para eventos específicos. Quando tratamos dos eventos, estes podem ser tanto um INSERT quanto um UPDATE, ou mesmo um DELETE. Assim, podemos definir determinadas operações que serão realizadas sempre que o evento ocorrer.

Quando nos referirmos a uma operação com uma trigger, esta é conhecida por trigger de função ou trigger function. Lembre-se que trigger e função de trigger são duas coisas diferentes, onde a primeira pode ser criada utilizando a instrução CREATE TRIGGER, enquanto que a última é definida pelo comando CREATE FUNCTION. Em linhas gerais, com as triggers definimos qual tarefa executar, e com as triggers de função definimos como essa tarefa será realizada.

Ao temos uma grande quantidade de acessos ao banco de dados por múltiplas aplicações, a utilização das triggers é de grande utilidade, e com isso, podemos manter a integridade de dados complexos, além de podermos acompanhar as mudanças ou o log a cada modificação ocorrida nos dados presentes numa tabela.

Para começar vamos criar de forma abstrata uma trigger function e uma trigger, como podemos ver apresentadas nas Listagens 1 e 2. As triggers functions podem ser definidas em linguagens compatíveis ao PostgreSQL, como PL/pgSQL, PL/Python, PL/Java dentre outros. Para esse artigo usaremos a linguagem PL/pgSQL.

CREATE OR REPLACE FUNCTION trigger_function_name RETURNS trigger AS $ExemploFuncao$ BEGIN /* Aqui definimos nossos códigos.*/ RETURN NEW; END; $ExemploFuncao
Listagem 1. Criação da estrutura de uma trigger function
CREATE [ CONSTRAINT ] TRIGGER NAME { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_NAME [ FROM referenced_table_NAME ] [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE PROCEDURE function_NAME ( arguments ) -- Quando um evento for declarado com: INSERT UPDATE [ OF column_NAME [, ... ] ] DELETE TRUNCATE
Listagem 2. Sintaxe de uma trigger simples

Repare que uma trigger function é, na realidade, uma função no PostgreSQL, mas com a diferença de que ela não recebe argumentos, e sim uma estrutura de dados especial chamada de TriggerData. Repare também que o seu tipo de retorno é a trigger, onde ela é chamada automaticamente no momento da ocorrência dos eventos (que podem ser INSERT, UPDATE, DELETE ou TRUNCATE). Com o PostgreSQL temos dois tipos de trigger disponíveis: trigger de nível de linha (row-level Trigger) e a trigger a nível de instrução (statement level trigger). Ambos são especificados com a utilização das cláusulas FOR EACH ROW (nível gatilho de linha) e FOR EACH STATEMENT, respectivamente. A utilização delas pode ser definida de acordo com a quantidade de vezes que a trigger deverá ser executada. Por exemplo, se uma instrução UPDATE for executada, e esta afetar seis linhas, temos que a trigger de nível de linha será executada seis vezes, enquanto que a trigger a nível de instrução será chamada apenas uma vez por instrução SQL.

Quando utilizamos triggers podemos conectá-las tanto a tabelas quanto a Views, de forma que as triggers são executadas para as tabelas em duas situações: BEFORE e AFTER, para qualquer uma das instruções DML (INSERT, UPDATE, DELETE), além de também possibilitar a sua execução utilizando a declaração TRUNCATE.

Quando temos a trigger definida com a instrução INSTEAD OF podemos utilizar as DML’s para as Views. As triggers serão disparadas antes ou depois das instruções DML, mas podem ser definidas apenas a nível de instrução. Já quando utilizamos o INSTEAD OF nas instruções DML, podemos executá-las apenas a nível de linha.

Com relação aos demais parâmetros, temos o NAME, que é utilizado para atribuirmos um nome para a trigger, o qual deve ser distinto das demais triggers criadas para a mesma tabela. A instrução table_NAME apresenta o nome da tabela em uso.

Quanto aos eventos (events), estes podem ser INSERT, UPDATE, DELETE ou TRUNCATE, os quais especificam o evento que irá disparar a trigger.

A expressão condition é uma expressão booleana que determina se a trigger function será executada. Se a condição WHEN for especificada, a função será chamada se a condição retornar true. Além disso, ela pode ser referida a colunas que contenham os valores antigos e se quer passar os novos valores. Para isso são utilizadas as instruções OLD.column_NAME ou NEW.column_NAME, respectivamente. Lembre-se que as function_names são funções fornecidas pelos usuários.

Por último, temos os arguments, que são listas opcionais de argumentos separados por vírgulas que podem ser fornecidos para a função quando a trigger for executada.

Para demonstrarmos o procedimento de criação das triggers e sua utilização criaremos alguns exemplos simples.

Para isso, criaremos uma nova base de dados, a qual chamaremos de DbTeste e em seguida criaremos uma tabela Funcionarios que conterá os campos da Listagem 3. Neste nosso exemplo queremos manter atualizados todos os registros adicionados para uma possível funcionarios_auditoria.

CREATE TABLE funcionarios ( nome character varying(100) NOT NULL, email character varying(200) NOT NULL, telefone character(14) NOT NULL, profissao character varying(150) NOT NULL, endereco character varying(100) NOT NULL, salario real )
Listagem 3. Criando a tabela de testes Funcionarios

Após a criação da nossa tabela principal criaremos uma nova tabela com o nome de funcionarios_funcionarios_auditoria, que será responsável por manter o histórico das alterações realizadas nos registros, como podemos ver na Listagem 4.

CREATE TABLE funcionarios_funcionarios_auditoria ( codigo_func INT NOT NULL, data_alteracao TEXT NOT NULL );
Listagem 4. Criação da tabela funcionarios_funcionarios_auditoria

Observe que há apenas dois campos: o código do funcionário e a data da alteração/criação do registro, que receberá uma data no formato Timestamp no momento em que o registro for criado na tabela de Funcionários.

Para darmos seguimento aos nossos testes iremos inserir alguns dados, como podemos ver na Listagem 5.

INSERT INTO FUNCIONARIOS (codigo, nome, email, telefone, profissao, endereco, salario) VALUES (1, 'Edson Dionisio', 'edson.dionisio@gmail.com', '(81)997402800', 'Desenvolvedor Web', 2000.00); INSERT INTO FUNCIONARIOS (codigo, nome, email, telefone, profissao, endereco, salario) VALUES (2, 'Marilia Késsia', 'mkessia.dionisio@gmail.com', '(81)997402844', 'Analista de desenvolvimento', 'rua testes', 6000.00); INSERT INTO FUNCIONARIOS (codigo, nome, email, telefone, profissao, endereco, salario) VALUES (3, 'Caroline França', 'carol@gmail.com', '(81)997402800', 'Analista de testes', 'rua testes', 2500.00); INSERT INTO FUNCIONARIOS (codigo, nome, email, telefone, profissao, endereco, salario) VALUES (4, 'João da Silva', 'joao@gmail.com', '(81)997401654', 'Analista de finanças', 'rua testes', 8000.00); INSERT INTO FUNCIONARIOS (codigo, nome, email, telefone, profissao, endereco, salario) VALUES (5, 'Maria das Dores', 'maria@gmail.com', '(81)997407845', 'Secretaria', 'rua testes', 1800.00);
Listagem 5. Inserindo dados na tabela de funcionarios

As triggers functions recebem, através de uma entrada especial, uma estrutura TriggerData, que possui um conjunto de variáveis locais que podemos usar nas nossas triggers functions. Dentre as variáveis presentes nesta estrutura temos as variáveis OLD e NEW, além de outras que começam com o prefixo TG_, como TG_TABLE_NAME.

A variável NEW é do tipo RECORD e contém uma nova linha a ser armazenada com base nos comandos INSERT/UPDATE das triggers a nível de linha.

Já a variável OLD também é do tipo RECORD e armazena a linha antiga quando utilizada com os comandos UPDATE/DELETE nas triggers de linha.

Após a criação das nossas tabelas definiremos uma trigger function, a qual chamaremos de funcionario_log_func, e será responsável por registrar as alterações feitas na tabela de funcionarios_auditoria depois de uma operação de INSERT na tabela funcionários, como apresentada pela Listagem 6.

CREATE OR REPLACE FUNCTION funcionario_log_func() RETURNS trigger AS $teste_trigger$ BEGIN INSERT INTO funcionario_funcionarios_auditoria (log_id, data_criacao) VALUES (new.codigo_func, current_timestamp); RETURN NEW; END; $teste_trigger
Listagem 6. Criação da trigger function funcionario_log_func

Com a nossa função criada, definiremos agora a nossa trigger e em seguida, a associaremos a tabela de funcionários, como podemos ver na Listagem 7.

CREATE TRIGGER log_trigger AFTER INSERT ON funcionarios FOR EACH ROW EXECUTE PROCEDURE funcionario_log_func();
Listagem 7. Criação da trigger log_trigger

Ao inserirmos um novo registro na nossa tabela de funcionários, podemos ver que um novo registro foi criado também na tabela de funcionarios_auditoria.

Para um exemplo um pouco mais complexo criaremos uma trigger contendo as três operações DML’s contidas numa mesma trigger function. Para isso, realizaremos inicialmente uma alteração na nossa tabelafuncionarios_auditoria, onde adicionaremos uma nova coluna operação_realizada para armazenar o nome da operação realizada.

Antes de prosseguirmos, excluiremos a tabela funcionarios_auditoria utilizando o comando DROP, como podemos ver a seguir:

Drop table funcionarios_auditoria cascade;

Após a exclusão da tabela, a criaremos novamente, mas contendo as seguintes colunas da Listagem 8.

CREATE TABLE funcionarios_auditoria ( log_id INT NOT NULL, data_criacao TEXT NOT NULL, operacao_realizada CHARACTER VARYING );
Listagem 8. Recriando a tabela funcionarios_auditoria

Neste momento podemos utilizar o comando apresentado na Listagem 9para criar ou recriar a nossa trigger function.

CREATE OR REPLACE FUNCTION funcionario_log_function() RETURNS trigger AS $BODY$ BEGIN -- Aqui temos um bloco IF que confirmará o tipo de operação. IF (TG_OP = 'INSERT') THEN INSERT INTO funcionarios_auditoria (log_id, data_criacao, operacao_realizada) VALUES (new.codigo_func, current_timestamp, ' Operação de inserção. A linha de código ' || NEW.codigo_func || 'foi inserido'); RETURN NEW; -- Aqui temos um bloco IF que confirmará o tipo de operação UPDATE. ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO funcionarios_auditoria (log_id, data_criacao, operacao_realizada) VALUES (NEW.codigo_func, current_timestamp, 'Operação de UPDATE. A linha de código ' || NEW.codigo_func || ' teve os valores atualizados ' || OLD || ' com ' || NEW.* || '.'); RETURN NEW; -- Aqui temos um bloco IF que confirmará o tipo de operação DELETE ELSIF (TG_OP = 'DELETE') THEN INSERT INTO funcionarios_auditoria (log_id, data_criacao, operacao_realizada) VALUES (OLD.codigo_func, current_timestamp, 'Operação DELETE. A linha de código ' || OLD.codigo_func || ' foi excluída '); RETURN OLD; END IF; RETURN NULL; END; $BODY
Listagem 9. Recriando a trigger function

Agora criaremos a trigger que será vinculada a tabela de funcionários, como podemos ver na Listagem 10.

CREATE TRIGGER trigger_log_todas_as_operacoes AFTER INSERT OR UPDATE OR DELETE ON funcionarios FOR EACH ROW EXECUTE PROCEDURE funcionario_log_function();
Listagem 10. Criação da tabela de funcionários

Para termos os resultados armazenados na nossa tabela utilizando as operações DML, utilizaremos as instruções de INSERT, UPDATE e DELETE, de acordo com a Listagem 11.

INSERT INTO funcionarios (codigo, nome, email, telefone, profissao, endereco, salario) VALUES (6, 'João da Silva 2', 'joaozinho@gmail.com', '(81)997445854', 'Analista de testes', 'rua desespero', 4500.00); UPDATE funcionarios set nome = 'Caroline Dionisio' WHERE codigo_func = '3'; DELETE FROM funcionarios WHERE codigo_func= 5;
Listagem 11. Utilizando as operações DML

Veja que a inserção dos registros na tabela de funcionarios_auditoria com as informações do código dos registros, data de atualização e o tipo de operação, foi realizada. Para que possamos ver os resultados basta utilizarmos a instrução SELECT, como apresentada a seguir:

SELECT log_id, data_criacao FROM funcionarios_auditoria;

Trabalhando com as Views e as triggers

Neste momento veremos um pouco sobre a utilização das Views em conjunto com as triggers. Para isso criaremos um novo exemplo com a tabela chamada funcionario_view e uma View chamada view_funcionarios. A tabela funcionario_view está na Listagem 12.

CREATE TABLE funcionario_view ( codigo_func INT NOT NULL, nome VARCHAR(100), email VARCHAR(100) );
Listagem 12. Criação da tabela funcionario_view

Com a tabela criada, vamos adicionar alguns dados de testes, como podemos ver na Listagem 13.

INSERT INTO funcionario_view VALUES (1, 'Edson', 'edson.dionisio@gmail.com '); INSERT INTO funcionario_view VALUES (2, 'Marília', 'mkessia@teste.com'); INSERT INTO funcionario_view VALUES (3, 'Caroline', 'carol@teste.com'); INSERT INTO funcionario_view VALUES (4, 'Gustavo', 'gustavo@teste.com'); INSERT INTO funcionario_view VALUES (5, 'Maria', 'maria@teste.com');
Listagem 13. Inserção de registros de teste

Após a inserção dos dados de testes criamos a View com base nos dados inseridos da seguinte forma:

CREATE VIEW view_funcionarios AS SELECT * FROM funcionario_view;

Essa foi a forma mais simples de apresentarmos uma View, mas podemos fazer de forma diferente. Vamos criar uma View que será atualizada sempre que uma trigger for disparada, como podemos ver na Listagem 14.

CREATE FUNCTION atualiza_view_trigger() RETURNS trigger AS $ BEGIN IF (TG_OP = 'INSERT') THEN INSERT INTO funcionario_view VALUES (NEW.codigo_func, NEW.nome, NEW.email); RETURN NEW; END IF; RETURN NULL; END;
Listagem 14. Criando uma trigger de atualização de View

Em seguida, criamos uma trigger e vinculamos a View view_funcionarios com o código da Listagem 15.

CREATE TRIGGER dispara_trigger_func INSTEAD OF INSERT ON view_funcionarios FOR EACH ROW EXECUTE PROCEDURE atualiza_view_trigger();
Listagem 15. Criação da trigger dispara_trigger_func

Para que possamos ver os resultados contidos na View view_funcionarios utilizamos a instrução SELECT da seguinte forma:

SELECT * FROM view_funcionarios;

Na view_funcionarios vamos inserir um novo registro, como segue o exemplo:

INSERT INTO view_funcionarios VALUES (6, 'Joao inserido através da view', 'view@teste.com');

Mas o registro foi inserido na tabela funcionário_view, certo? Para vermos o resultado, utilizaremos o comando SELECT> e veremos que nosso registro foi inserido corretamente, como segue:

SELECT * FROM funcionario_view;

Para que possamos ver todas as triggers que temos adicionadas no nosso banco de dados podemos utilizar o seguinte comando:

SELECT * FROM pg_trigger;

Caso a intenção seja ver todas as triggers para uma tabela específica, então a query deverá ser de acordo com a apresentada a seguir:

SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid = pg_class.oid AND relname = 'funcionarios';
Listagem 1. NOME

O relname é funcionários, pois estamos utilizando esta base de dados.

Para finalizarmos, caso tenhamos interesse em excluir as triggers, podemos usar o seguinte comando:

DROP TRIGGER trigger_log_todas_as_operacoes ON funcionarios;

Trabalhando com Rules no PostgreSQL

Podemos usar o sistema de regras do PostgreSQL, por exemplo, para reconfigurar um comando para null caso o valor de uma coluna não apareça na tabela, mas não podemos usar esse sistema na implementação de tipos de restrições, como a utilização de chaves estrangeiras.

Assim como com as triggers, podemos atualizar Views com as rules do PostgreSQL, o que será o nosso foco neste momento.

Quando temos implementações que podem ser realizadas tanto por rules quanto por triggers, a garantia de qual será melhor utilizada depende da utilização do banco de dados. No caso das triggers, estas são disparadas uma vez para cada linha afetada, o que difere de uma rule, pois esta modifica a consulta ou gera uma consulta adicional. Neste caso, se tivermos a intenção de atingir várias linhas, a utilização de uma rule será muito mais rápida do que a trigger.

Para começarmos com nosso exemplo iremos excluir a View funcionarios_view da Listagem 12 e, em seguida, a criaremos novamente. Em seguida podemos inserir os dados presentes na Listagem 13 para realizarmos nossos testes. Não se esqueça de criar a View funcionarios_view novamente.

Com toda essa etapa realizada, criaremos a nossa primeira rule para a inserção de registros com o nome de view_funcionarios_rule e o seu código está na Listagem 16.

CREATE RULE view_funcionarios_rule AS ON INSERT TO view_funcionarios DO INSTEAD (INSERT INTO funcionarios_view VALUES (NEW.codigo_func, NEW.nome, NEW.email));
Listagem 16. Criação da rule view_funcionarios_rule

Para conhecermos as rules criadas, podemos realizar uma consulta na tabela catalogo, chamada pg_rewrite, para vermos se nossa rule foi devidamente gerada utilizamos a seguinte instrução:

SELECT rulename FROM pg_rewrite WHERE rulename='view_funcionarios_rule';

Feito isso, podemos realizar a inserção de um novo registro na View view_funcionario com base na seguinte instrução:

INSERT INTO view_funcionarios VALUES (6, 'Edson 2', 'edson@teste.com');

Em seguida podemos realizar uma consulta na view para ver se os dados foram inseridos corretamente. A diferença aqui é que, ao invés de utilizarmos triggers, utilizamos rules para realizar a operação de inserção.

Com isso finalizamos este artigo, onde tivemos uma abordagem mais prática sobre utilização das triggers e um pouco sobre as rules, que tornam as implementações mais rápidas em comparação as triggers, em alguns casos.

Saiu na DevMedia!

Saiba mais sobre Triggers e SQL Server ;)

Artigos relacionados