Criando gatilhos INSTEAD OF

A principal vantagem dos gatilhos INSTEAD OF é que eles habilitam exibições que não seriam atualizáveis para oferecer suporte a atualizações. Uma exibição baseada em várias tabelas base deve usar um gatilho INSTEAD OF para oferecer suporte a inserções, atualização e exclusões que referenciem dados em mais de uma tabela. Outra vantagem dos gatilhos INSTEAD OF é que eles o habilitam a codificar lógica que pode rejeitar partes de um lote e, ao mesmo tempo, permitir que outras partes do lote tenham êxito.

Um gatilho INSTEAD OF pode realizar ações como:

• Ignorar partes de um lote.

• Não processar uma parte de um lote e registrar as linhas com problemas.

• Realizar uma ação alternativa quando uma condição de erro é encontrada.

  Observação
  Os gatilhos INSTEAD OF DELETE e INSTEAD OF UPDATE não podem ser definidos em uma tabela que tenha uma chave estrangeira definida com uma ação em cascata DELETE ou UPDATE.

Codificar esta lógica como parte de um gatilho INSTEAD OF evita que todos os aplicativos que acessem os dados tenham de reimplementar a lógica.

Exemplo

Na seqüência de instruções Transact-SQL a seguir, um gatilho INSTEAD OF atualiza duas tabelas base de uma exibição. Adicionalmente, são mostradas as seguintes abordagens para controlar erros:

• As inserções duplicadas na tabela Person são ignoradas e as informações da inserção é registrada na tabela PersonDuplicates.

• As inserções de duplicadas na EmployeeTable são transformadas em uma instrução UPDATE que recupera as informações atuais em EmployeeTable sem gerar uma violação de chave duplicada.

As instruções Transact-SQL criam duas tabelas base, uma exibição, uma tabela para registro de erros e o gatilho INSTEAD OF na exibição. As tabelas seguintes separam dados pessoais e empresariais e são as tabelas base para a exibição.

CREATE TABLE Person
   (
    SSN         char(11) PRIMARY KEY,
    Name        nvarchar(100),
    Address     nvarchar(100),
    Birthdate   datetime
   )

CREATE TABLE EmployeeTable
   (
    EmployeeID       int PRIMARY KEY,
    SSN              char(11) UNIQUE,
    Department       nvarchar(10),
    Salary           money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
   )

A exibição a seguir informa todos os dados pertinentes das duas tabelas para uma pessoa.

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
       Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

Você pode registrar tentativas de inserir linhas com números de seguro social duplicados. A tabela PersonDuplicates registra os valores inseridos, o nome do usuário que tentou a inserção e a hora da inserção.

CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )

O gatilho INSTEAD OF insere linhas em várias tabelas base de uma exibição única. As tentativas de inserir linhas com números de documentos pessoais duplicados são registradas na tabela PersonDuplicates. As linhas duplicadas na EmployeeTable são alteradas para atualizar as instruções.

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

Consulte também

Conceitos

Criando gatilhos DML