• Articolo

Progettazione di trigger INSTEAD OF

Il vantaggio principale dei trigger INSTEAD OF consiste nel fatto che consentono alle viste non aggiornabili di supportare gli aggiornamenti. Una vista basata su più tabelle di base deve utilizzare un trigger INSTEAD OF per supportare inserimenti, aggiornamenti ed eliminazioni che fanno riferimento a dati inclusi in più tabelle. Un altro vantaggio dei trigger INSTEAD OF è rappresentato dal fatto che consentono di scrivere il codice in modo da rifiutare parti di un batch accettandone altre.

Un trigger INSTEAD OF consente di eseguire le operazioni seguenti:

  • Ignorare parti di un batch.

  • Evitare di elaborare una parte di un batch e registrare le righe con problemi.

  • Intraprendere un'azione alternativa quando si rileva una condizione di errore.

    Nota

    I trigger INSTEAD OF DELETE e INSTEAD OF UPDATE non possono essere definiti in una tabella contenente una chiave esterna definita utilizzando un'operazione di propagazione DELETE o UPDATE.

La scrittura del codice come parte di un trigger INSTEAD OF evita alle applicazioni che accedono ai dati di dover implementare nuovamente il codice.

Esempio

Nella sequenza di istruzioni Transact-SQL seguenti un trigger INSTEAD OF aggiorna due tabelle di base in una vista. Vengono inoltre illustrate le strategie di gestione degli errori seguenti:

  • Gli inserimenti duplicati inclusi nella tabella Person vengono ignorati e le informazioni dall'inserimento vengono registrate nella tabella PersonDuplicates.

  • Gli inserimenti di duplicati in EmployeeTable vengono convertiti in un'istruzione UPDATE che recupera le informazioni correnti in EmployeeTable senza generare una violazione della chiave duplicata.

Tramite le istruzioni Transact-SQL vengono create due tabelle di base, una vista, una tabella per registrare gli errori e il trigger INSTEAD OF nella vista. Nelle tabelle seguenti vengono separati i dati personali da quelli aziendali. Si tratta delle tabelle di base per la vista.

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)
   )

Nella vista seguente sono indicati tutti i dati pertinenti relativi a una persona inclusi nelle due tabelle.

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

È possibile registrare i tentativi di inserimento di righe con codici fiscali duplicati. Nella tabella PersonDuplicates vengono registrati i valori inseriti, il nome dell'utente che ha tentato l'inserimento e l'ora dell'inserimento.

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

Il trigger INSTEAD OF inserisce righe in più tabelle di base da una singola vista. I tentativi di inserimento di righe con codici fiscali duplicati vengono registrati nella tabella PersonDuplicates. Le righe duplicate in EmployeeTable vengono modificate in istruzioni di aggiornamento.

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

Vedere anche

Concetti

Progettazione di trigger DML