Programação em Transact-SQL: Triggers/Gatilhos

Laboratório 5
Base de Dados II
2008/2009
Plano de Trabalho
Lab. 4: Programação em Transact-SQL
Referências
MICROSOFT SQL SERVER
- Triggers (gatilhos).
- Exercícios
1. Conceito.
- Os Stored Procedures permitem realizar a maior parte das operações envolvidas em
actividades de administração do dia-a-dia dum sistema de gestão de bases de dados.
- Contudo, estes procedimentos não são reactivos em relação a eventos tais como
saber se este ou aquele registo de dados de uma tabela foi inserido, alterado ou
simplesmente removido.
- Os gatilhos (ou triggers) são procedimentos especiais que são sensíveis a eventos,
ou seja, que reagem a eventos. Têm por função garantir as regras de negócio ou de
integridade no momento em que ocorrem eventos de modificação de dados numa
base de dados.
- A grande diferença entre procedimentos e gatilhos está na forma como eles são
invocados. Os procedimentos são invocados directa e explicitamente através das
ferramentas do SQL Server ou de uma qualquer aplicação cliente, ao passo que os
gatilhos são activados a partir de eventos de actualização (INSERT, UPDATE e
DELETE) que ocorram em tabelas ou vistas.
- Um gatilho faz parte integrante da definição duma tabela para a qual foi definido,
sendo só invocado quando uma das operações de actualização da tabela é efectuada.
- Os utilizadores não têm qualquer controlo sobre a invocação e consequente
execução de um gatilho, já que é o SQL Server que desencadeia a sua execução.
- Os gatilhos são tratados como transações, ou seja, se acontecer um problema, é
desfeito tudo que esse trigger tiver feito.
- A utilização de gatilhos é vantajosa em determinadas circunstâncias :
- Podem ser utilizados para efectuar alterações em cascade; por exemplo, se
apagar a factura eliminar automaticamente todas as linhas.
- Podem ser utilizados para colocar restrições mais complexas que as definidas
numa instrução de check, pois podem utilizar validações de colunas que tenham
ligação com outras tabelas (ao contrário do check).
- O gatilho tem a possibilidade de saber o estado do registo antes e depois de
uma determinada alteração, e efectuar acções de acordo com esse estado.
- Em resumo: um gatilho é um procedimento que é automaticamente executado em
resposta a um determinado evento. Esses eventos poder ser de INSERT, UPDATE
ou DELETE. Aliás, é possível executar um gatilho diferente por cada evento de
INSERT, UPDATE ou DELETE.
2. Sintaxe.
CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
sql_statement [...n]
}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (column)
[{AND | OR} UPDATE (column)]
[...n]
| IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}
Arguments
trigger_name
Is the name of the trigger. A trigger name must conform to the rules for identifiers and
must be unique within the database. Specifying the trigger owner name is optional.
table
Is the table on which the trigger is executed; sometimes called the trigger table.
Specifying the owner name of the table is optional. Views cannot be specified.
WITH ENCRYPTION
Encrypts the syscomments entries that contain the text of CREATE TRIGGER.
{ [DELETE] [,] [INSERT] [,] [UPDATE] } | { [INSERT] [,] [UPDATE]}
Are keywords that specify which data modification statements, when attempted against
this table, activate the trigger. At least one option must be specified. Any combination
of these in any order is allowed in the trigger definition. If more than one option is
specified, separate the options with commas.
WITH APPEND
Specifies that an additional trigger of an existing type should be added. Use of this
optional clause is needed only when the compatibility level is less than or equal to 65. If
the compatibility level is greater than or equal to 70, the WITH APPEND optional
clause is not needed to add an additional trigger of an existing type (this is the default
behavior of CREATE TRIGGER with the compatibility level setting greater than or
equal to 70.) .
NOT FOR REPLICATION
Indicates that the trigger should not be executed when a replication process modifies the
table involved in the trigger.
AS
Are the actions the trigger is to take.
sql_statement
Is the trigger condition(s) and action(s). Trigger conditions specify additional criteria
that determine whether the attempted DELETE, INSERT, or UPDATE statements
cause the trigger action(s) to be carried out.
The trigger actions specified in the Transact-SQL statements go into effect when the
user action (DELETE, INSERT, or UPDATE) is attempted.
Triggers can include any number and kind of Transact-SQL statements except
SELECT. A trigger is designed to check or change data based on a data modification
statement; it should not return data to the user. The Transact-SQL statements in a
trigger often include control-of-flow language. A few special tables are used in
CREATE TRIGGER statements:
• deleted and inserted are logical (conceptual) tables. They are structurally
similar to the table on which the trigger is defined (that is, the table on which the
user action is attempted) and hold the old values or new values of the rows that
may be changed by the user action. For example, to retrieve all values in the
deleted table, use:
SELECT *
FROM deleted
n
Is a placeholder indicating that multiple Transact-SQL statements can be included in
the trigger. For the IF UPDATE (column) statement, multiple columns can be included
by repeating the UPDATE (column) clause.
IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used with
DELETE operations. More than one column can be specified. Because the table name is
specified in the ON clause, do not include the table name before the column name in an
IF UPDATE clause. To test for an INSERT or UPDATE action for more than one
column, specify a separate UPDATE(column) clause following the first one.
UPDATE(column) can be used anywhere inside the body of the trigger.
column
Is the name of the column to test for either an INSERT or UPDATE action. This
column can be of any data type supported by SQL Server.
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or
columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or updated.
COLUMNS_UPDATED can be used anywhere inside the body of the trigger.
bitwise_operator
Is the bitwise operator to use in the comparison.
updated_bitmask
Is the integer bitmask of those columns actually updated or inserted. For example, table
t1 contains columns C1, C2, C3, C4, and C5. To check whether columns C2, C3, and
C4 are all updated (with table t1 having an UPDATE trigger), specify a value of 14. To
check whether only column C2 is updated, specify a value of 2.
comparison_operator
Is the comparison operator. Use the equals sign (=) to check whether all columns
specified in updated_bitmask are actually updated. Use the greater than symbol (>) to
check whether any or some of the columns specified in updated_bitmask are updated.
column_bitmask
Is the integer bitmask of those columns to check whether they are updated or inserted.
3. Criação.
Podemos criar mais do que um gatilho por tabela ou vista, mas devemos saber que nesta
circunstância não temos grande controlo sobre a ordem pela qual os gatilhos são
disparados.
Apesar disto, o SQL Server dá-nos a possibilidade de indicar quais dos gatilhos são o
primeiro e o último a serem activados. Isto consegue-se à custa do procedimento
sp_settriggerorder.
O SQL Server permite definir 2 tipos diferentes de gatilhos:
•
•
AFTER. Só é disparado após todas as operações especificadas na caracterização do
gatilho terem sido executadas com sucesso. Este é o tipo de gatilho por omissão.
Podemos criar quantos gatilhos quisermos deste tipo para cada uma das operações
de INSERT, DELETE e UPDATE sobre uma tabela, mas não sobre uma vista.
INSTEAD OF. Estes gatilhos são executados em vez da instrução Transact-SQL de
manipulação de dados que provocou a sua activação. Podemos definir no máximo
um gatilho deste tipo para cada uma das operações de INSERT, DELETE e
UPDATE sobre uma tabela ou uma vista.
Há 2 formas de criar gatilhos no SQL Server:
• Procedure Wizard. Não existe!
• Enterprise Manager.
• Query Analyser. Há 4 maneiras possíveis:
- Sem template.
- Menu Principal. File -> New -> escolher template na pasta Create Trigger.
- Barra de ferramentas. Ícone New -> Create Trigger -> escolher template de
trigger.
- Navegador de objectos. Situa-se na sub-janela à esquerda do Query
Analyser. Aí podemos escolher o template do trigger.
Sintaxe simplificada.
CREATE TRIGGER <nome do gatilho>
ON <tabela>
FOR <tipo de operação> AS
<Bloco de Instruções SQL>
EXERCÍCIOS I
(base de dados Livro&Livro)
Exercício 1. (Query Analyser. Sem template. Gatilho para INSERT).
Crie um gatilho que lance uma mensagem de alerta sempre que seja lançada uma nova
promoção de livros na Livro&Livro.
-- T-SQL 6.21:
-- Gatilho que alerta o lançamento de uma nova promoção de
-- um livro.
-- Gatilho para a geração de alertas de promoções de livros.
CREATE TRIGGER AlertaLançamentoPromoção
ON [Livros-Promoções]
FOR INSERT AS
RAISERROR ('Inserção de uma nova promoção de livros na
base de dados.',16, 1)
---
Este gatilho é activado quando é executada uma operação de inserção sobre a tabela LivrosPromoções. A instrução RAISEERROR é utilizada para apresentar mensagens de erro
definidas pelo utilizador. Neste caso, a mensagem de erro é simplesmente uma mensagem
de alerta.
Exercício 2. (Gatilho para INSERT)
Crie um gatilho de actualização do acumulado de compras de um cliente.
-- T-SQL 6.22: Gatilho de actualização do acumulado de
-- compras de um cliente.
--- Gatilho para actualizar o acumulado de compras de um
-- cliente, após o registo de uma nova venda.
CREATE TRIGGER [inserçãoVenda] ON [dbo].[Vendas]
FOR INSERT
AS
UPDATE Clientes
SET [Valor-Acumulado-Compras] = [Valor-Acumulado-Compras] +
(SELECT [Total-C/-Imposto] FROM inserted)
WHERE Número = (SELECT Cliente FROM inserted)
---
Porquê é que usámos a tabela inserted em vez da tabela Vendas na
inquirição embutida SELECT [Total-C/-Imposto] FROM inserted ?
A tabela inserted conjuntamente com a tabela deleted são tabelas
especiais do SQL Server. As funções são, respectivamente, o
armazenamento de uma cópia do último registo inserido e do último registo
removido da tabela em que o gatilho está definido.
No nosso exercício acima, a tabela inserted contém o último registo inserido
na tabela de Vendas.
Exercício 3. (Exercício 2 só com um SELECT e variáveis locais. Gatilho para INSERT)
Neste caso, são as variáveis locais as responsáveis pelo transporte dos valores da tabela
Vendas utilizados na instrução UPDATE.
-- T-SQL 6.23: Utilização de variáveis locais na implementação de um
-- gatilho.
--- Gatilho para actualizar o acumulado de compras de um cliente, após o
-- registo de uma nova venda.
CREATE TRIGGER [inserçãoVenda] ON [dbo].[Vendas]
FOR INSERT
AS
DECLARE @ClienteVenda int, @TotalVenda money
SELECT @ClienteVenda = Cliente, @TotalVenda = [Total-C/-Imposto]
FROM inserted
UPDATE Clientes
SET [Valor-Acumulado-Compras] = [Valor-Acumulado-Compras]
+ @TotalVenda
WHERE Número = @ClienteVenda
---
Exercício 4. (Gatilho para DELETE)
Crie um gatilho para acerto do valor acumulado de compras de um cliente quando uma das
suas vendas é removida da base de dados.
-- T-SQL 6.24 ñ Gatilho para acerto do valor acumulado de compras
-- de um cliente.
--- Gatilho para acerto do valor acumulado de compras de um cliente.
CREATE TRIGGER [anulaçãoVenda] ON [dbo].[Vendas]
FOR DELETE
AS
UPDATE Clientes
SET [Valor-Acumulado-Compras] = [Valor-Acumulado-Compras]
- (SELECT [Total-C/-Imposto] FROM deleted)
WHERE Número = (SELECT Cliente FROM deleted)
---
Exercício 5. (Gatilho para UPDATE)
Crie um gatilho para corrigir os dados duma venda.
CREATE TRIGGER [anulaçãoVenda] ON [dbo].[Vendas]
FOR UPDATE
AS
UPDATE Clientes
SET [Valor-Acumulado-Compras] = [Valor-Acumulado-Compras]
- (SELECT [Total] FROM deleted)
+ (SELECT [Total] FROM inserted)
WHERE Número = (SELECT Cliente FROM deleted)
Neste exemplo podemos observar a utilização das duas tabelas especiais. A tabela deleted é
usada para retirar o valor errado da venda à tabela de clientes, ao passo que a tabela
inserted é usada para fornecer o valor correcto da venda.