Banco de Dados SQL TRIGGERS (Gatilhos)

1. Banco de DadosSQLTRIGGERS (Gatilhos) Elaini Simoni Angelotti

2. TRIGGERS (GATILHOS) • Uma TRIGGER é um tipo especial de sp que é executado automaticamente em conseqüência de uma modificação (INSERT, UPDATE, DELETE) na tabela na qual a TRIGGER foi configurada. • Chama-se disparar a trigger a execução automática da mesma • Não podem ser executadas usando EXEC.

3. Uma TRIGGER é sempre associada a uma tabela, porém os comandos que formam a TRIGGER podem acessar dados de outras tabelas. • Ex: dadas as tabelas Nota_Fiscal(Num_nota, valor_total) Produto(Cod_Prod, nome, preço, estoque) Nota_Prod(Num_nota, Cod_Prod, quantidade) Pode-se criar uma Trigger para a operação de INSERT na tabela Nota_Prod. Sempre que for inserido um novo item de pedido na tabela Nota_Prod será disparada um Trigger que atualiza o nível de estoque do produto que está sendo vendido

4. Com o uso de TRIGGERs pode-se definir “Regras de Negócio” do BD • Representam regras do “mundo real” • Ex: Aprovar financiamento maiores que um determinado valor • Pode-se usar TRIGGERS para exclusão e atualização em cascata • Se o comando que está sendo executado violar a definição de uma CONSTRAINT definida, a TRIGGER não irá disparar

5. NO SQLServer 2000/2005 existem alguns tipos de TRIGGERS: • DELETE • UPDATE AFTER • INSERT • INSTEAD OF • AFTER: é disparada APÓS todos os comandos de uma TRIGGER associada com um DELETE, UPDATE e INSERT terem sidos executados • INSTEAD OF é disparada ANTES dos comandos serem executados. Processa as constraints antes da execução da trigger.

6. O SQLServer 2000/2005 permite especificar TRIGGERs em Views (INSTEAD OF) • Nos comandos que definem a TRIGGER pode-se usar a maioria dos comandos SQL, inclusive estruturas IF..ELSE e WHILE. • Não são permitidos os seguintes comandos: ALTER DATABASE, CREATE DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RESTORE DATABASE, RESTORE LOG, RECONFIGURE

7. Os comandos que compõe a TRIGGER tem acesso a duas tabelas especiais: • DELETED TABLE • INSERTED TABLE • Essas tabelas existem apenas na memória do servidor, não sendo gravadas em disco • Os registros dessas tabelas são acessíveis somente durante a execução da TRIGGER • Para referenciar essas tabelas temporárias dentro da TRIGGER usa-se os nomes • DELETED • INSERTED

8. A tabela DELETED armazena cópias de registros afetados por um comando DELETE ou UPDATE • Armazena os registros antes da alteração • A tabela INSERTED armazena cópias dos registros afetados por um comando INSERT ou UPDATE. • Os registros na tabela INSERTED são cópias dos novos registros da tabela da tabela que disparou a TRIGGER

9. Sintaxe: CREATE TRIGGER nome_da_trigger ON nome_da_tabela ou nome_da_view [WITH ENCRYPTION] {FOR | AFTER| INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]} AS comando 1 comando 2 ..... comando n

10. Exemplo: Criar uma TRIGGER que evite que sejam inseridos novos clientes na tabela CLIENTE (banco de dados LOCADORA) em que o compo UF seja igual a ‘AC’ ou ‘PA’. Essa TRIGGER será criada para a ação INSERT. CREATE TRIGGER TG_Permite_UF ON Cliente FOR INSERT AS IF EXISTS (SELECT * FROM INSERTED WHERE UF_CLI IN ('PA', 'AC')) BEGIN PRINT 'INSERÇÃO DE REGISTRO CANCELADA.' PRINT 'ESTADO (UF) PROIBIDO!!' ROLLBACK END ELSE PRINT 'PAÍS PERMITIDO!'

11. Crie uma TRIGGER calcule e insera a data de devolução prevista na tabela EMP_DEV sempre que uma fita for emprestada CREATE TRIGGER tg_CALCULA_DATA_DEV_PREV ON EMP_DEV FOR INSERT AS IF EXISTS (SELECT * FROM INSERTED) BEGIN UPDATE EMP_DEV SET DATA_DEV_PREV = DATEADD(DD,1,DATA_EMP) END

12. Crie uma TRIGGER que calcule e insira a data de devolução prevista na tabela EMP_DEV sempre que uma fita for emprestada. Se a fita for de catálogo ela tem dois dias para ser entregue. Lançamentos podem ficar locadas apenas 1 dia. alter table Fita add Tipo_fita varchar (10) Constraint CKTipo_fita check (Tipo_fita in ('catálogo','Lançamento')) DROP TRIGGER tg_CALCULA_DATA_DEV_PREV

13. CREATE TRIGGER tg_CALCULA_DATA_DEV_PREV ON EMP_DEV FOR INSERT AS IF EXISTS (SELECT * FROM INSERTED INNER JOIN FITAON FITA.COD_FITA = INSERTED.COD_FITA WHERE Tipo_fita = 'catálogo') BEGIN UPDATE EMP_DEV SET DATA_DEV_PREV = DATEADD(DD,2,DATA_EMP) WHERE emp_dev.cod_fita = (select inserted.cod_fita from inserted) END ELSE IF EXISTS (SELECT * FROMINSERTED INNER JOIN FITA ON FITA.COD_FITA = INSERTED.COD_FITA WHERE Tipo_fita = 'Lançamento') BEGIN UPDATE EMP_DEV SET DATA_DEV_PREV = DATEADD(DD,1,DATA_EMP) WHERE emp_dev.cod_fita = (select inserted.cod_fita from inserted) END

14. Crie uma TRIGGER que calcule o valor da multa de um cliente sempre que o mesmo devolver a fita à locadora. Isso significa que toda a vez que o campo “dev_efet” for preenchido (UPDATE) a multa será calculada. CREATE TRIGGER tg_CALCULA_MULTA ON EMP_DEV FOR UPDATE AS IF UPDATE (DATA_DEV_EFET) BEGIN UPDATE EMP_DEV SET multa = 1.5 * DATEDIFF(DD,DATA_DEV_PREV,DATA_DEV_EFET) WHERE DATEDIFF(DD,DATA_DEV_PREV,DATA_DEV_EFET) > 0 END IF UPDATE (DATA_DEV_EFET) BEGIN UPDATE EMP_DEV SET multa = 0 WHEREDATEDIFF(DD,DATA_DEV_PREV,DATA_DEV_EFET) <= 0 END

15. Vamos supor que, por ordem da admistraçao não seja permitido fazer alterações e inserções na tabela Fornecedor. Para garantir esta norma implemente um trigger que dispare em resposta a comandos UPDATE e INSERT na tabela Fornecedor. Esta trigger deve emitir um aviso de que as alterações e inserções foram suspensas e registrar em uma tabela o nome do usuário que tentou fazer a alteração e o nome do fornecedor que tentou-se alterar ou inserir. CREATE TABLE TENTOU_ALTERAR ( FORNECEDOR VARCHAR (50) NOT NULL, USUÁRIO CHAR (30) NOT NULL )

16. CREATE TRIGGER Tg_NÃO_ALTERAINSERE_FORNECEDOR ON FORNECEDOR FOR INSERT, UPDATE AS -- VARIAVEL QUE SERÁ UTILIZADA NA TRIGGER DECLARE @NOME_FORNECEDOR VARCHAR(50) -- VERIFICA SE FOI FEITA ALGUMA ALTERAÇÃO (INSERT OU UPDATE) IF EXISTS (SELECT * FROM DELETED) BEGIN SET @NOME_FORNECEDOR = (SELECT NOME_FORN FROMDELETED) PRINT 'VC NÃO PODE ALTERAR O REGISTRO DE UM FORNECEDOR' ROLLBACK INSERT INTO TENTOU_ALTERAR VALUES (@NOME_FORNECEDOR, CURRENT_USER) END IF EXISTS (SELECT * FROMINSERTED) BEGIN SET @NOME_FORNECEDOR = (SELECT NOME_FORN FROMINSERTED) PRINT 'VC NÃO PODE INSERIR NOVOS FORNECEDORES' ROLLBACK INSERT INTO TENTOU_ALTERAR VALUES (@NOME_FORNECEDOR, CURRENT_USER) END

17. Habilitando e Desabilitando Trigger • Para desabilitar temporariamente uma trigger: ALTER TABLE Nome_da_Tabela DISABLE TRIGGER Nome_da_Trigger • Para habilitar novamente uma trigger: ALTER TABLE Nome_da_Tabela ENABLE TRIGGER Nome_da_Trigger