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
Personvengono ignorati e le informazioni dall'inserimento vengono registrate nella tabellaPersonDuplicates. - Gli inserimenti di duplicati in
EmployeeTablevengono convertiti in un'istruzione UPDATE che recupera le informazioni correnti inEmployeeTablesenza 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