Usare le tabelle inserite ed eliminate

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Nelle istruzioni dei trigger DML vengono usate due tipi di tabelle speciali, ovvero le tabella eliminate e le tabelle inserite. SQL Server crea e gestisce queste tabelle automaticamente. È possibile utilizzare queste tabelle temporanee residenti in memoria per verificare gli effetti di determinate modifiche apportate ai dati e impostare le condizioni per le azioni dei trigger DML. Non è possibile modificare i dati o eseguire operazioni DDL (Data Definition Language), quale CREATE INDEX, direttamente nelle tabelle.

Informazioni sulle tabelle inserite ed eliminate

Nei trigger DML le tabelle inserted e deleted vengono principalmente utilizzate per eseguire le operazioni seguenti:

• Estendere l'integrità referenziale tra le tabelle.

• Inserire o aggiornare i dati in tabelle di base sottostanti alla vista.

• Verificare la presenza di errori ed eseguire le azioni appropriate sulla base dell'errore rilevato.

• Individuare le differenze tra lo stato di una tabella prima della modifica dei dati e lo stato della tabella stessa dopo la modifica, per eseguire le azioni appropriate sulla base di tali differenze.

La tabella eliminata archivia le copie delle righe interessate nella tabella di trigger prima che vengano modificate da un'istruzione DELETE o UPDATE (la tabella di trigger è la tabella in cui viene eseguito il trigger DML). Durante l'esecuzione di un'istruzione DELETE o UPDATE, le righe interessate vengono prima eliminate dalla tabella di trigger e quindi trasferite nella tabella eliminata.

La tabella inserita archivia le copie delle righe nuove o modificate dopo un'istruzione INSERT o UPDATE. Durante l'esecuzione di un'istruzione INSERT o UPDATE, le righe nuove o modificate nella tabella di trigger vengono copiate nella tabella inserita. Le righe della tabella inserita sono copie delle righe nuove o aggiornate della tabella di trigger.

Una transazione di aggiornamento è simile a un'operazione di eliminazione seguita da un'operazione di inserimento. Durante l'esecuzione di un'istruzione UPDATE, si verifica la sequenza di eventi seguente:

1. La riga originale viene copiata dalla tabella di trigger alla tabella eliminata.
2. La tabella di trigger viene aggiornata con i nuovi valori dell'istruzione UPDATE.
3. La riga aggiornata nella tabella di trigger viene copiata nella tabella inserita.

In questo modo è possibile confrontare il contenuto della riga prima dell'aggiornamento (nella tabella eliminata) con i nuovi valori di riga dopo l'aggiornamento (nella tabella inserita).

Quando si impostano le condizioni di trigger, utilizzare correttamente le tabelle inserted e deleted in base all'azione che ha attivato il trigger. Sebbene il riferimento alla tabella deleted durante la verifica di un'istruzione INSERT o alla tabella inserted durante la verifica di un'istruzione DELETE non causi alcun errore, in questi casi le tabelle di verifica dei trigger non conterranno alcuna riga.

SQL Server non consente i riferimenti di colonna di tipo text, ntext o image nelle tabelle inserite ed eliminate per i trigger AFTER. Questi tipi di dati sono tuttavia disponibili per garantire la compatibilità con le versioni precedenti. L'archiviazione preferita per i dati di grandi dimensioni consiste nell'usare i tipi di dati varchar(max), nvarchar(max)e varbinary(max) . I trigger AFTER e INSTEAD OF supportano i dati varchar(max), nvarchar(max)e varbinary(max) nelle tabelle inserted e deleted. Per altre informazioni, vedere CREATE TRIGGER (Transact-SQL).

Esempio di utilizzo della tabella inserita in un trigger per l'applicazione delle regole di business

Poiché i vincoli CHECK possono fare riferimento solo alle colonne in cui è definito il vincolo a livello di colonna o di tabella, è necessario definire come trigger qualsiasi vincolo tra tabelle, in questo caso le regole business.

Nell'esempio seguente viene creato un trigger DML. Questo trigger verifica che la posizione creditizia del fornitore sia buona quando viene eseguito un tentativo di inserimento di un nuovo ordine di acquisto nella tabella PurchaseOrderHeader . Per ottenere la posizione creditizia del fornitore corrispondente all'ordine di acquisto inserito, la tabella Vendor deve essere una tabella con riferimenti e deve essere unita in join alla tabella inserted. Se la posizione creditizia è troppo bassa, viene visualizzato un messaggio e l'operazione di inserimento non viene eseguita.

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

Usare le tabelle inserite ed eliminate nei trigger INSTEAD OF

Alle tabelle inserted e deleted passate ai trigger INSTEAD OF definiti nelle tabelle vengono applicate le stesse regole valide per le tabelle inserted e deleted passate ai trigger AFTER. Il formato delle tabelle inserted e deleted è uguale a quello della tabella in cui è stato definito il trigger INSTEAD OF. Ogni colonna delle tabelle inserted e deleted è mappata direttamente a una colonna della tabella di base.

Le regole seguenti relative al fatto che un'istruzione INSERT o UPDATE che fa riferimento a una tabella con un trigger INSTEAD OF debba fornire valori per le colonne sono applicabili anche alle tabelle senza trigger INSTEAD OF:

• Non è possibile specificare valori per colonne calcolate o colonne con tipo di dati timestamp .

• Non è possibile specificare valori per colonne con proprietà IDENTITY, a meno che IDENTITY_INSERT non sia impostata su ON per la tabella. Se IDENTITY_INSERT è impostata su ON, le istruzioni INSERT devono fornire un valore.

• Le istruzioni INSERT devono fornire valori per tutte le colonne NOT NULL a cui sono applicati vincoli DEFAULT.

• Ad eccezione delle colonne calcolate, identity o timestamp , i valori sono facoltativi per tutte le colonne che supportano valori Null o per le colonne NOT NULL con definizione DEFAULT.

Quando un'istruzione INSERT, UPDATE o DELETE fa riferimento a una vista con trigger INSTEAD OF, il motore di database esegue una chiamata al trigger invece di eseguire azioni sulle tabelle. Il trigger deve utilizzare le informazioni visualizzate nelle tabelle inserted e deleted per compilare le istruzioni necessarie per implementare l'azione richiesta nelle tabelle di base, anche nel caso in cui il formato delle informazioni nelle tabelle inserted e deleted compilate per la vista sia diverso dal formato dei dati delle tabelle di base.

Il formato delle tabelle inserted e deleted passate a un trigger INSTEAD OF definito in una vista corrisponde all'elenco di selezione dell'istruzione SELECT definita per la vista. Ad esempio:

USE AdventureWorks2022;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;  

Il set di risultati di questa vista ha tre colonne, ovvero una colonna int e due colonne nvarchar . Le tabelle inserted e deleted passate a un trigger INSTEAD OF definito nella vista hanno a loro volta una colonna int chiamata BusinessEntityID, una colonna nvarchar chiamata LNamee una colonna nvarchar chiamata FName.

L'elenco di selezione di una vista può inoltre includere espressioni di cui non è stato eseguito il mapping diretto a una singola colonna della tabella di base. È possibile che alcune espressioni di vista, ad esempio una chiamata di funzione o costante, non facciano riferimento ad alcuna colonna e vengano pertanto ignorate. Le espressioni complesse possono fare riferimento a più colonne, ma nelle tabelle inserted e deleted è disponibile un solo valore per ogni riga inserita. Lo stesso vale per le espressioni semplici di una vista che fanno riferimento a una colonna calcolata con un'espressione complessa. Un trigger INSTEAD OF nella vista deve essere in grado di gestire questo tipo di espressioni.

Considerazioni sulle prestazioni

Poiché le tabelle inserite ed eliminate sono tabelle virtuali residenti in memoria, le proprietà come statistiche o indici non sono disponibili. Anche se alcune informazioni sulla cardinalità vengono esposte da queste tabelle, è consigliabile prestare attenzione quando si considera il numero di righe da archiviare temporaneamente. L'inserimento di un numero elevato di righe in queste tabelle e l'interrogazione o unione con altre tabelle può comportare piani di query non ottimali ed esecuzioni delle query lente. Assicurarsi di progettare e testare attentamente l'applicazione per soddisfare le esigenze di prestazioni delle query.

Passaggi successivi

Per altre informazioni, vedere Trigger DML.