Utilizzare le tabelle inserite ed eliminate

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureDatabase SQL in Microsoft Fabric

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 si possono modificare direttamente i dati nelle tabelle né eseguire operazioni nel linguaggio di definizione dei dati (DDL) sulle tabelle, come CREATE INDEX.

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 memorizza copie delle righe interessate nella tabella dei trigger prima che venissero modificate da un'istruzione DELETE o UPDATE (la tabella dei trigger è la tabella su cui viene eseguito il trigger DML). Durante l'esecuzione di un'istruzione DELETE or UPDATE , le righe interessate vengono prima copiate dalla tabella di trigger e trasferite nella tabella eliminata.

La tabella inserita memorizza copie delle nuove righe o delle modifiche dopo un'istruzione INSERT op.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 , avviene la seguente sequenza di eventi:

1. La riga originale viene copiata dalla tabella di trigger alla tabella eliminata.
2. La tabella dei 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 eliminata durante il test di una INSERT o alla tabella inserita durante il test di una DELETE non causi errori, queste tabelle di test di trigger non contengono righe in questi casi.

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 appena inserito, è necessario fare riferimento alla tabella Vendor e unirla in join con la tabella appena inserita. 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
        INNER 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;
    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 delle righe 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" è lo stesso del formato della tabella su cui è definito il trigger INSTEAD OF. Ogni colonna delle tabelle inserted e deleted è mappata direttamente a una colonna della tabella di base.

Le seguenti regole riguardanti quando un'istruzione INSERT o UPDATE che fa riferimento a una tabella con un trigger INSTEAD OF deve fornire valori per colonne sono le stesse che se la tabella non avesse un trigger INSTEAD OF:

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

• I valori non possono essere specificati per colonne con una IDENTITY proprietà, a meno che IDENTITY_INSERT non sia ON per quella tabella. Quando IDENTITY_INSERT è ON, le INSERT istruzioni devono fornire un valore.

• INSERT le istruzioni devono fornire valori per tutte le colonne NOT NULL che non hanno DEFAULT vincoli.

• Per qualsiasi colonna tranne quelle calcolate, identità o timestamp , i valori sono opzionali per qualsiasi colonna che ammetta nulli, o per qualsiasi colonna NOT NULL che abbia una DEFAULT definizione.

Quando un INSERT, UPDATE, o DELETE un'istruzione fa riferimento a una vista che ha un trigger INSTEAD OF, il motore di database chiama il trigger invece di agire direttamente contro qualsiasi tabella. 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 è conforme 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 LName e 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 la panoramica di Trigger DML.