Introdução

2008.2
Triggers
Renata Viegas
2008.2
Gatilhos (Triggers)
• Utilizados para implementar regras de
negócio da aplicação
• Sua principal aplicação é a criação de
restrições e consistências de acesso ao
banco de dados
2008.2
Gatilhos (Triggers)
• Exemplo: Ao invés de restringir contas
com saldos negativos, podemos querer
ativar uma ação que automaticamente
inicia um empréstimo para aquela conta
2008.2
Gatilhos (Triggers)
• Um trigger é um conjunto de
comandos SQL que é automaticamente
disparado quando um comando INSERT,
UPDATE ou DELETE é executado em
uma tabela.
2008.2
Gatilhos (Triggers)
• Um trigger é uma regra do tipo E_C_A:
– E: Evento
– C: Condição a ser satisfeita na presença do
evento E
– A: Ação a ser tomada caso a condição C
seja satisfeita
2008.2
Gatilhos (Triggers)
• Voltando ao nosso exemplo:
– Ao invés de restringir contas com saldos
negativos, podemos querer ativar uma ação que
automaticamente inicia um empréstimo para
aquela conta.
• Evento: operação de modificação do saldo
• Condição: se saldo < 0
• Ação: Criar um empréstimo para conta
2008.2
Gatilhos (Triggers)
• Composição de um trigger
– Um nome: único para cada banco de dados
– A maneira: um comando INSERT, UPDATE ou DELETE
– As instruções: Um bloco de comandos SQL
• Vínculo de um trigger
– Os gatilhos (triggers) são sempre vinculados a uma
determinada tabela
– Quando uma tabela é removida, todos os gatilhos
relacionados serão excluídos automaticamente.
2008.2
Gatilhos no SQL Server
• Funcionamento
– Quando um trigger é executado, o SQL Server cria duas
tabelas temporárias que ficam residentes na memória e
existem apenas enquanto o trigger estiver ativo.
– As duas tabelas contêm a mesma estrutura da tabela de
base onde o trigger foi criado.
• Essas duas tabelas recebem o nome de INSERTED e DELETED.
• Você pode consultar dados nessa tabela com o SELECT, da
mesma forma que uma tabela real
2008.2
Gatilhos no SQL Server
• Funcionamento (cont.)
– Quando um comando INSERT é executado, o registro
criado será copiado para a tabela de base e para a tabela
INSERTED.
– Quando um comando UPDATE é executado, o registro
original (registro antigo) é armazenado na tabela
DELETED e o registro modificado (registro atual) é
armazenado na tabela de base e na tabela INSERTED.
– Quando um comando DELETE é executado, o registro
excluído é armazenado na tabela DELETED.
2008.2
Gatilhos no SQL Server
• Os momentos de disparo são definidos para
atuarem quando o respectivo comando de
modificação for executado
– Um trigger de INSERT não vai ser disparado quando a
tabela sofre um UPDATE
2008.2
Gatilhos (Triggers)
• Sintaxe :
CREATE TRIGGER <nome> ON <tabela>
FOR [INSERT][,][UPDATE][,][DELETE]
AS <comandos SQL>
2008.2
Gatilhos (Triggers)
Create Table ExemploTrigger (
Id Int
Campo Varchar(10))
2008.2
Gatilhos (Triggers)
Create Trigger Exemplo On ExemploTrigger
For Insert, Update, Delete
As
Begin
If Exists (Select Id From Deleted)
Begin
If Exists (Select Id From Inserted)
Begin
Print 'Foi feito um Update'
End
Else
Begin
Print 'Foi feito um Delete'
End
End
Else
Begin
Print 'Foi feito um insert'
End
End
2008.2
Gatilhos (Triggers)
Create Table Estoque
(Id Int,
Produto Char(10),
Qtd Int,
QtdMinima Int)
Create Table Compras (Id Int, Qtd Int)
Create
Table
Vendas
(Id
Int,
Qtd
Int)
2008.2
Gatilhos (Triggers)
• Quando um produto é comprado, acrescenta-se um item do
produto no estoque (O estoque é atualizado
automaticamente)
Create Trigger TrCompras On Compras
For Insert As
Begin
Update Estoque
Set Estoque.Qtd = Estoque.Qtd + Inserted.Qtd
From Estoque, Inserted
Where Inserted.Id = Estoque.Id
End
2008.2
Gatilhos (Triggers)
• Ao vender um produto, a quantidade em estoque deve ser
decrementada. E deve-se comparar com sua quantidade
mínima em estoque.
Create Trigger TrVendas On Vendas
For Insert As
Begin
Update Estoque
Set Estoque.Qtd = Estoque.Qtd - Inserted.Qtd
From Estoque, Inserted Where Inserted.Id = Estoque.Id
If Exists (Select Id From Estoque Where Qtd <= QtdMinima)
PRINT 'Existem Produtos em Falta'
End
2008.2
Gatilhos (Triggers)
• Voltando ao nosso exemplo:
– Ao invés de restringir contas com saldos
negativos, podemos querer ativar uma ação que
automaticamente inicia um empréstimo para
aquela conta.
• Crie um Trigger !
conta (numero, titular, saldo)
emprestimo (cod_emprestimo, numConta, valor)
2008.2
Gatilhos (Triggers)
create trigger EmprestimoAuto on Conta
For Update
as
begin
If exists (select numero from inserted where saldo <= 0)
begin
declare @numC int, @valor money
Set @numC = (select numero from inserted
where saldo <= 0)
Set @valor = (select d.saldo + i.saldo from inserted i,
deleted d)
insert into emprestimo values (1, @numC, @valor)
print 'Foi feito um emprestimo automático'
end
end
2008.2
Gatilhos (Triggers)
• Observações importantes
– Triggers não podem ser criadas para Visões ou tabelas
temporárias
– Triggers não possuem parâmetros e não podem ser
explicitamente invocados.
• São disparados quando os dados da “tabela protegida” são
modificados
– Triggers são considerados como parte de uma transação
• Se houver falha no seu funcionamento, os comandos serão
revertidos (ROLLBACK)
2008.2
Gatilhos no SQL Server
• Detalhe…
– No SQL Server, os trigger são disparados após
um INSERT, UPDATE ou DELETE
– Conclusão: não existe o momento BEFORE
nem AFTER