Triggers (Gatilhos)

1. Triggers (Gatilhos) Professor Esp. Diego André Sant’Ana E-mail: diego.santana@ifms.edu.br Disciplina: Banco de Dados II professordiegosantana.wordpress.com

2. Triggers • Triggers são procedimentos armazenados que são acionados por algum evento e em determinado momento. Na maioria dos bancos de dados estes eventos podem ser inserções (INSERT), atualizações (UPDATE) e exclusões (DELETE), e os momentos podem ser dois: antes da execução do evento (BEFORE) ou depois (AFTER). E isso também vale para o PostgreSQL.

3. Triggers • Um diferencial das triggers deste banco de dados para outros é que no PostgreSQL as triggers são sempre associadas a funções de triggers (veremos isso mais adiante) e, nos demais, criamos o corpo da trigger na própria declaração desta.

4. Tipos Triggers • O PostgreSQL possui dois tipos de triggers: triggers-por-linha e triggers-por-instrução.

5. Tipos Triggers • A trigger-por-linha é disparada uma vez para cada registro afetado pela instrução que disparou a trigger. Já a trigger-por-instrução é disparada somente uma vez quando a instrução é executada.

6. Tipos Triggers • Funções de trigger e linguagens procedurais • Funções de triggers são funções que não recebem nenhum parâmetro e retornam o tipo trigger. Essas funções recebem uma estrutura chamada TriggerData, e esta é passada internamente para a função pelo PostgreSQL.

7. Tipos Triggers • O PostgreSQL disponibiliza duas variáveis importantes para serem usadas em conjunto com as triggers-por-linha: NEW e OLD. • A variável NEW, no caso do INSERT, armazena o registro que está sendo inserido. No caso do UPDATE, armazena a nova versão do registro depois da atualização.

8. Tipos Triggers • A variável OLD, no caso do DELETE, armazena o registro que está sendo excluído. No caso do UPDATE, armazena a antiga versão do registro depois da atualização.

9. Tipos Triggers • As funções de triggers devem ser escritas em C (linguagem C) ou alguma linguagem procedural disponível no banco de dados. Essas liguagens podem ser várias, como Ruby, Perl, Python, entre outras. Atualmete existem quatro linguagens procedurais disponíveis na distribuição padrão do PostgreSQL: PL/pgSQL, PL/Tcl, PL/Perl e PL/Python. Mas é possível que o usuário defina outras linguagens. Para instalar novas linguagens no PostgreSQL, consulte a documentação oficial do PostgreSQL, que pode ser obtida no seguinte endereço: http://pgdocptbr.sourceforge.net/.

10. Tipos Triggers • Cada linguagem, que suporta triggers, possui o seu próprio método para tornar os dados de entrada da trigger disponíveis para a função. Estes dados de entrada incluem o tipo de evento da trigger, assim como as opções informadas na criação da trigger. Para uma trigger no nível de linha, os dados de entrada também incluem as linhas NEW para as triggers de INSERT e UPDATE, e a linha OLD para os triggers de UPDATE e DELETE.

11. Exemplo de triggers • CREATETRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] } ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTEPROCEDUREnome_da_função ( argumentos )

12. Exemplo de triggers • Argumentos: • nome é o nome da trigger. • before| after determina se a função será chamada antes ou depois do evento. • evento indica em que momento a trigger será disparada. A trigger pode ser dispara antes ou depois de um evento de DELETE, UPDATE ou INSERT. • tabela indica em qual tabela a trigger estará associada. • row | statement especifica se a trigger deve ser disparada uma vez para cada linha afetada pelo evento ou apenas uma vez por comando SQL. Se não for especificado nenhum dos dois, o padrão é FOR EACH STATEMENT. • nome_da_função especifica a função de trigger.

13. Exemplo de triggers • Um exemplo de criação de uma trigger é apresentado abaixo: • CREATE TRIGGER "u_tg_validaCpf " BEFORE INSERT ON CLIENTE FOR EACH ROW EXECUTE PROCEDURE u_fn_validaCpf();

14. Alter trigger • Alterando uma trigger • A sintaxe do comando para alterar uma trigger é apresentada abaixo: • ALTER TRIGGER nome ON tabela RENAME TO novo_nome • Argumentos: • nome é nome do gatilho existente a ser alterado. • tabela é o nome da tabela onde o gatilho atua. • novo_nome é o novo nome do gatilho.

15. Excluir trigger • Excluindo uma trigger • Para excluir uma trigger basta executar o comando abaixo: • DROP TRIGGER nome ON tabela [ CASCADE | RESTRICT ] • Argumentos: • nome é o nome do gatilho a ser removido. • tabela é o nome da tabela para a qual o gatilho está definido. • [ CASCADE | RESTRICT ] indica se ao remover a trigger vamos remover também todos os objetos que dependem dela (CASCADE) ou recusaremos sua exclusão (RESTRICT).

16. Desabilitar trigger • Para desabilitar uma trigger execute o comando abaixo: • ALTER TABLE nome_tabela DISABLE TRIGGER nome_trigger • Para desabilitar todas as triggers da tabela, execute o seguinte comando: • ALTER TABLE nome_tabela DISABLE TRIGGER ALL

17. Desabilitar as trigger • Para desabilitar uma trigger execute o comando abaixo: • ALTER TABLE nome_tabela DISABLE TRIGGER nome_trigger • Para desabilitar todas as triggers da tabela, execute o seguinte comando: • ALTER TABLE nome_tabela DISABLE TRIGGER ALL

18. Crie uma tabela CREATE TABLE trig.empresa ( nome_empresatext, salariointeger, ultima_datatimestamp, ultimo_usuariotext );

19. Função de checagem CREATE FUNCTION trig.empresa_check() RETURNS trigger AS $$ BEGIN -- Checar o nome da empresa e o salario IF NEW.nome_empresa IS NULL THEN RAISE EXCEPTION 'nome da empresanaopodesernulo '; END IF; IF NEW.salario IS NULL THEN RAISE EXCEPTION '% nãopodertersalarionulo ', NEW.nome_empresa ; END IF; -- Checar se é negativo IF NEW.salario < 0 THEN RAISE EXCEPTION '% salarionãopodesernegativo', NEW.nome_empresa; END IF; -- Colocaosusuario e a data da alteração NEW.ultima_data := current_timestamp; NEW.ultimo_usuario := current_user; RETURN NEW; END; $$ LANGUAGE plpgsql;

20. Cria uma trigger • CREATE TRIGGER empresa_check • BEFORE INSERT OR UPDATE ON trig.empresa FOR EACH ROW EXECUTE PROCEDURE trig.empresa_check();

21. INSERT na tabela com trigger insert into trig.empresa (nome_empresa,salario) values(null,null) insert into trig.empresa (nome_empresa,salario) values('IFMS',null) insert into trig.empresa (nome_empresa,salario) values('IFMS',-200) insert into trig.empresa (nome_empresa,salario) values('IFMS',200) update trig.empresa set salario=2000 select * from trig.empresa

22. Outro exemplo CREATE TABLE trig.emp ( nome_empresatext NOT NULL, salario integer ); CREATE TABLE trig.emp_audit( operacaochar(1) NOT NULL, data_alttimestamp NOT NULL, user_idtext NOT NULL, nome_empresatext NOT NULL, salario integer );

23. Criar uma função que retorna uma trigger CREATE OR REPLACE FUNCTION trig.auditar_empresa() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Cria uma linha em emp_audit para refletir a operação em emp e fazer usando a variavel--TG_OP para trabalhar a operacao IF (TG_OP = 'DELETE') THEN INSERT INTO trig.emp_audit(operacao,data_alt,user_id,nome_empresa,salario) Values( 'D', now(), user, OLD.nome_empresa,OLD.salario); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO trig.emp_auditSELECT 'U', now(), user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO trig.emp_auditSELECT 'I', now(), user, NEW.*; RETURN NEW; END IF; RETURN NULL; -- resultado e idnorado desde que o depois da trigger END; $emp_audit$ LANGUAGE plpgsql;

24. Criando a trigger • CREATE TRIGGER emp_audit • AFTER INSERT OR UPDATE OR DELETE ON trig.emp • FOR EACH ROW EXECUTE PROCEDURE trig.auditar_empresa();

25. Criando a trigger • INSERT INTO TRIG.EMP VALUES('ifms',2000) • update trig.emp set salario=5000 where nome_empresa='ifms' • delete from trig.emp where nome_empresa='ifms' • select * from trig.emp • select * from trig.emp_audit();

26. REFERÊNCIAS http://imasters.com.br/artigo/10644/postgresql/triggers-no-postgresql/ http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Definidas_pelo_Usu%C3%A1rio_e_Triggers/Triggers