Júlio Battisti Livros e Cursosa sua sala de aula na internet

Para criar triggers utilizamos o comando CREATE TRIGGER.

Sintaxe básica para o comando CREATE TRIGGER:

CREATE TRIGGER nome_da_trigger

ON {tabela ou view}

[WITH ENCRYPTION]

{FOR | AFTER | INSTEAD OF}

{[DELETE] [,] [INSERT] [,] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

  comando 1

  comando 2

  comando 3

  ...

  comando n

Com este comando podemos criar triggers associadas com qualquer dos comandos básicos – DELETE, INSERT ou UPDATE –, e também triggers do tipo INSTEAD OF e AFTER. Uma trigger pode ser associada com mais de uma opção, por exemplo, podemos criar uma trigger que dispara em resposta a um comando INSERT e também a um comando UPDATE.

Nos comandos que definem a trigger (comando 1, ..., comando n), podemos incluir a maioria dos comandos, inclusive as estruturas IF...ELESE e WHILE. Não são permitidos os seguintes comandos:

• ALTER DATABASE
• CREATE DATABASE
• DISK INIT
• DISK RESIZE
• DROP DATABASE
• LOAD DATABASE
• LOAD LOG
• RECONFIGURE
• RESTORE DATABASE
• RESTORE LOG

Ao criarmos uma trigger, informações sobre a trigger são armazenadas nas tabelas de sistema, sysobjects e syscomments.

Vamos ver alguns exemplos práticos. À medida que formos implementando os exemplos, vou explicando novos conceitos e comandos que forem sendo utilizados.

Exemplo prático 1: Vamos iniciar com um exemplo simples. Vamos criar uma trigger que evita que sejam inseridos novos clientes na tabela Clientes, do Banco de Dados NwindAccess, em que o campo País seja igual a China ou Indonésia. Esta trigger será criada para a ação INSERT. Vamos chamá-la de tg_ExcluiChinaIndonésia. Esta trigger verifica se o campo País é igual a China ou Indonésia. Em caso afirmativo, um Roll back será chamado para cancelar a inserção do registro, caso contrário a transação será concluída.

Para criar a trigger tg_ExcluiChinaIndonésia, siga os passos indicados a seguir:

1. Abra o SQL Server Management Studio e navegue até o banco de dados NwindAccess, da instância SERVIDOR\SQL2005.

2. Clique com o botão direito do mouse no banco de dados NwindAccess e, no menu de opções que é exibido, clique em New Query.

3. Será aberta a janela de execução de comandos T-SQL, sendo o banco de dados NwindAccess, o banco de dados atual.

4. Execute o seguinte comando:

USE NwindAccess
GO
CREATE TRIGGER tg_ExcluiChinaIndonésia
ON Customers
FOR INSERT
AS
IF EXISTS(SELECT * FROM inserted WHERE Country IN (‘China’,‘Indonésia’))
     PRINT ‘PAÍS PROIBIDO !!!’
ELSE
   PRINT ‘PAÍS PERMITIDO’

Observe a utilização da função EXISTS(comando SQL). Esta função retorna verdadeiro, caso o comando SQL retorne algum registro, e falso, caso contrário.

Agora vamos testar o funcionamento da trigger, utilizando um comando INSERT.

4. Execute o seguinte comando:

USE NwindAccess
GO
INSERT INTO [Clientes]
        ( [CódigoDoCliente],[NomeDaEmpresa],[NomeDoContato],[CargoDoContato],
        [Endereço],[Cidade],[Região],[CEP], [País], [Telefone], [Fax])
 VALUES
       (‘xxxxx’,‘Empresa ABC Ltda.’,‘José da Silva’,‘Gerente’,
        ‘Rua Riachuelo – 80’,‘Santa Maria’,‘Centro’,‘97050-010',‘China’,‘220-1335',‘220-1314')

Ao executarmos este comando recebemos a seguinte mensagem:

PAÍS PROIBIDO !!!

(1 row(s) affected)

Mas, ao invés da mensagem “PAÍS PROIBIDO !!!’, nós queremos que a inserção do registro seja cancelada, caso o país seja China ou Indonésia. Para isso, vamos alterar a trigger tg_ExcluiChinaIndonésia. Ao invés da mensagem, vamos dar um Roll back. Como a operação INSERT e a trigger ocorrem no contexto de uma transação, conforme descrito anteriormente, ao cancelarmos a transação iremos também cancelar a trigger e também o comando INSERT.

Para alterar uma trigger, utilizamos o comando ALTER TRIGGER. A sintaxe básica para este comando é a seguinte:

USE NwindAccess

GO

ALTER TRIGGER nome_trigger

ON nome_tabela

FOR { INSERT | UPDATE | DELETE }

AS

comando 1

comando 2

comando 3

...

comando n

5. Para alterar a trigger tg_ExcluiChinaIndonésia, utilizamos o seguinte comando:

USE NwindAccess
GO
ALTER TRIGGER tg_ExcluiChinaIndonésia
ON Clientes
FOR INSERT
AS
IF EXISTS(SELECT * FROM inserted WHERE País IN (‘China’,‘Indonésia’))
  BEGIN
       PRINT ‘INSERÇÃO DO REGISTRO CANCELADA !!!!!!’
       ROLLBACK
   END
ELSE
   PRINT ‘PAÍS PERMITIDO’

Agora vamos tentar inserir um cliente em que o campo Country é igual a Indonésia. Vamos verificar se a trigger foi disparada e depois daremos um SELECT para verificar se a inserção do registro, foi realmente cancelada.

Para inserir um registro em que o país é Indonésia, execute o seguinte comando:

USE NwindAccess

GO

INSERT INTO [Clientes]

( [CódigoDoCliente],[NomeDaEmpresa],[NomeDoContato],[CargoDoContato],
     [Endereço],[Cidade],[Região],[CEP], [País], [Telefone], [Fax])
VALUES

      (‘ABC12’,‘Empresa 123 Ltda.’,‘Para Pedro’,‘Gerente’,

      ‘Rua Riachuelo – 444’,‘Porto Alegre’,‘Centro’,‘97051-230',‘Indonésia’,‘220-4444',’223-1344')

Este comando retorna a seguinte mensagem:

INSERÇÃO DO REGISTRO CANCELADA !!!!!!

6. Vamos conferir se o comando INSERT realmente foi cancelado. Para verificar se o registro para a Indonésia não foi inserido, execute o seguinte comando:

USE Northwind
GO
SELECT * FROM Customers
WHERE CustomerID=‘ABC12'

O resultado será:

(0 row(s) affected)

Isto comprova que o comando INSERT, para o cliente ABC12, foi realmente cancelado.

Veja que agora temos uma trigger, que dispara sempre que tentarmos inserir um registro na tabela Clientes. Esta trigger verifica se o registro que está sendo inserido possui um valor igual a China ou Indonésia, no campo País. Caso o registro tenha um destes valores no campo País, a inserção do novo registro será cancelada através da utilização de um comando ROLLBACK. O comando ROLLBACK cancela a execução da transação, da qual fazem parte a trigger e o comando INSERT que disparou a trigger. Como o comando INSERT faz parte da transação que está sendo cancelada, este é cancelado e a inserção do registro também.

7. Mantenha aberta a janela de execução de comandos T-SQL

Com este exemplo vimos, na prática, a ação de uma trigger. Agora, vamos a mais alguns exemplos, para ilustrar outros tipos de triggers.

Exemplo prático 2: Vamos supor que, por ordem da administração, não devem ser feitas alterações nem inserções na tabela Clientes. Para garantir que esta norma seja respeitada, você deve implementar uma trigger, que dispare em resposta aos comandos INSERT e UPDATE na tabela Clientes. Esta trigger deve emitir um aviso que as alterações e inserções de clientes estão suspensas e registrar em uma tabela, chamada TentouAlterar, o nome do cliente que tentou-se alterar ou inserir e também o nome do usuário logado que tentou fazer a inserção ou alteração. Vamos chamar esta trigger de tg_NãoAlteraInsereCliente. A tabela TentouAlterar terá a estrutura indicada na Tabela 10.2.

Tabela 10.2 Estrutura da tabela TentouAlterar.

Para criar a trigger tg_NãoAlteraInsereCliente, siga os passos indicados a seguir:

1. Você já deve estar com a janela de execução de comandos T-SQL aberta. O primeiro passo é criar a tabela TentouAlterar. Para isso, execute o seguinte comando:

USE NwindAccess
CREATE TABLE TentouAlterar
(
Cliente             nvarchar(50) NOT NULL,
Usuário             char(30)     NOT NULL
)

2. Para criar a trigger tg_NãoAlteraInsereCliente, utilize o seguinte comando:

USE NwindAccess
GO
CREATE TRIGGER tg_NãoAlteraInsereCliente
ON Clientes
FOR INSERT,UPDATE
AS

-- Variável que será utilizada na trigger
DECLARE @NomeCliente       nvarchar(50)

-- Verificamos se foi feita uma alteração.
IF EXISTS(SELECT * FROM deleted)
   BEGIN
       -- Defino o valor da variável @NomeCliente
       SET @NomeCliente = (SELECT NomeDaEmpresa FROM deleted)
       INSERT INTO [TentouAlterar]
           ([Cliente],[Usuário])
           VALUES
          (@NomeCliente,CURRENT_USER)
           PRINT ‘VOCÊ NÃO PODE ALTERAR O REGISTRO DE UM CLIENTE !!!!’
           ROLLBACK
   END

-- Verifico se foi feita uma inserção.
ELSE
IF EXISTS(SELECT * FROM inserted)
    BEGIN
       -- Defino o valor da variável @NomeCliente
       SET @NomeCliente = (SELECT NomeDaEmpresa FROM inserted)
       INSERT INTO [TentouAlterar]
               ([Cliente],[Usuário])
           VALUES
        (@NomeCliente,CURRENT_USER)
          PRINT ‘VOCÊ NÃO PODE INSERIR NOVOS CLIENTES!!!!’
          ROLLBACK
   END
ELSE
   BEGIN
       PRINT ‘ESTA MENSAGEM NUNCA SERÁ EXIBIDA !!!!’
   END

Observe que, na definição da trigger, associamos a trigger com as ações INSERT e UPDATE – FOR INSERT,UPDATE. Também observe a utilização, novamente, da função EXISTS. Também utilizamos a função CURRENT_USER para capturar o nome do usuário atualmente logado no SQL Server e que executou o comando INSERT ou UPDATE.

Agora vamos testar o funcionamento da trigger, utilizando um comando INSERT.

3. Execute o seguinte comando:

USE NwindAccess
GO
INSERT INTO [Clientes]

( [CódigoDoCliente],[NomeDaEmpresa],[NomeDoContato],[CargoDoContato],
        [Endereço],[Cidade],[Região],[CEP], [País], [Telefone], [Fax])
 VALUES
   (‘123hh’,‘Empresa hhh Ltda.’,‘José Pereira’,‘Gerente’,
   ‘Rua Riachuelo – 80’,‘Santa Maria’,‘Centro’,‘97050-010',‘China’,‘220-1335',‘220-1314')

Você receberá a seguinte mensagem:

INSERÇÃO DO REGISTRO CANCELADA !!!!!!

E agora, José? A mensagem não deveria ser:

‘VOCÊ NÃO PODE INSERIR NOVOS CLIENTES!!!!’

4. Acontece que temos uma outra trigger, criada anteriormente, chamada tg_ExcluiChinaIndonésia, a qual também está associada com a ação INSERT. Foi esta trigger que disparou, emitiu a mensagem – INSERÇÃO DO REGISTRO CANCELADA !!!!!! – e deu um Roll back na transação. Vamos excluir esta trigger. Para excluir uma trigger, utilizamos o comando DROP TRIGGER nome_trigger. Execute o seguinte comando:

USE NwindAccess
GO
DROP TRIGGER tg_ExcluiChinaIndonésia

7. Agora execute novamente o seguinte comando INSERT:

USE NwindAccess
GO
INSERT INTO [Clientes]

( [CódigoDoCliente],[NomeDaEmpresa],[NomeDoContato],[CargoDoContato],
        [Endereço],[Cidade],[Região],[CEP], [País], [Telefone], [Fax])
 VALUES
   (‘123hh’,‘Empresa hhh Ltda.’,‘José Pereira’,‘Gerente’,
   ‘Rua Riachuelo – 80’,‘Santa Maria’,‘Centro’,‘97050-010',‘China’,‘220-1335',‘220-1314')

Agora a mensagem é a seguinte:

(1 row(s) affected)

VOCÊ NÃO PODE INSERIR NOVOS CLIENTES!!!!

Esta mensagem é emitida pela trigger tg_NãoAlteraInsereCliente, a qual detectou que tentamos executar uma operação INSERT, na tabela Clientes e impediu que a operação fosse executada.

5. Vamos conferir se a operação realmente foi cancelada. Execute o seguinte comando:

USE NwindAccess

GO

SELECT * FROM Clientes

WHERE NomeDaEmpresa='123hh'

O resultado deve ser:

(0 row(s) affected)

Isto comprova que o registro não foi inserido.

Agora vamos testar se a trigger está realmente funcionando para alterações UPDATE.

6. Execute o seguinte comando:

USE NwindAccess
GO
-- Executo o UPDATE para disparar a trigger.
UPDATE [Clientes]
SET  [CódigoDoCliente]     = ‘XXX12’,
       [NomeDaEmpresa]     = ‘XXX Ltda.’,
       [NomeDoContato]     = ‘José Costa’,
       [CargoDoContato]    = ‘Gerente’,
       [Endereço]   = ‘Rua ABC – 123’,
       [Cidade]            = ‘Lajeado’,
       [Região]            = ‘Central’,
       [CEP]  = ‘11111-111’,
       [País] = ‘Brazil’,
       [Telefone]        = ‘111-1111’,
       [Fax]        = ‘111-1111’

WHERE [CódigoDoCliente]=‘RICAR’

O comando é executado e a seguinte mensagem é exibida:

(1 row(s) affected)

VOCÊ NÃO PODE ALTERAR O REGISTRO DE UM CLIENTE !!!!

Esta mensagem é emitida pela trigger tg_NãoAlteraInsereCliente, a qual detectou que tentamos executar uma operação UPDATE e impediu que a operação fosse executada.

7. Vamos conferir se a operação realmente foi cancelada. Execute o seguinte commando:

USE NwindAccess
GO
SELECT * FROM Clientes
WHERE CódigoDoCliente=‘XXX12'

O resultado deve ser:

(0 row(s) affected)

8. Isto comprova que o registro não foi alterado. Neste caso inclusive as alterações em cascata, feitas na tabela Pedidos, foram canceladas. Para comprovar este fato, execute o seguinte comando:

USE NwindAccess
GO
SELECT * FROM Pedidos
WHERE CódigoDoCliente=’XXX12'

O resultado deve ser:

(0 row(s) affected)

9. Mantenha a janela de execução de commandos T-SQL aberta.

Com estes exemplos, podemos ver como uma trigger é disparada e como a trigger interage com as tabelas de um Banco de Dados. A utilização de triggers, abre um leque de opções para a implementação de Regras de Negócio. Quanto mais conhecermos da linguagem T-SQL, mais resultados práticos poderemos obter com a utilização de triggers. Para uma referência completa da linguagem T-SQL, consulte o tópico SQL Server Language Reference -> Transact-SQL Reference, no Books OnLine.

Agora vamos aprender a criar triggers utilizando o SQL Server Management Studio.