Utilizzo delle tabelle inserite ed eliminateUse the inserted and deleted Tables

Nelle istruzioni dei trigger DML vengono usate due tabelle speciali, ovvero la tabella inserted e la tabella deleted.DML trigger statements use two special tables: the deleted table and the inserted tables. SQL ServerSQL Server crea e gestisce queste tabelle automaticamente. automatically creates and manages these tables. È 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.You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. Non è possibile modificare i dati o eseguire operazioni DDL (Data Definition Language), quale CREATE INDEX, direttamente nelle tabelle.You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

Nei trigger DML le tabelle inserted e deleted vengono principalmente utilizzate per eseguire le operazioni seguenti:In DML triggers, the inserted and deleted tables are primarily used to perform the following:

  • Estendere l'integrità referenziale tra le tabelle.Extend referential integrity between tables.

  • Inserire o aggiornare i dati in tabelle di base sottostanti alla vista.Insert or update data in base tables underlying a view.

  • Verificare la presenza di errori ed eseguire le azioni appropriate sulla base dell'errore rilevato.Test for errors and take action based on the error.

  • 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.Find the difference between the state of a table before and after a data modification and take actions based on that difference.

    Nella tabella deleted vengono archiviate copie delle righe interessate dall'esecuzione delle istruzioni DELETE e UPDATE.The deleted table stores copies of the affected rows during DELETE and UPDATE statements. Durante l'esecuzione di un'istruzione DELETE o UPDATE, le righe vengono eliminate dalla tabella di trigger e trasferite nella tabella deleted.During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. In genere, la tabella deleted e la tabella di trigger non hanno righe in comune.The deleted table and the trigger table ordinarily have no rows in common.

    Nella tabella inserted vengono archiviate copie delle righe interessate dall'esecuzione delle istruzioni INSERT e UPDATE.The inserted table stores copies of the affected rows during INSERT and UPDATE statements. Durante una transazione INSERT o UPDATE, le nuove righe vengono aggiunte sia alla tabella inserted che alla tabella di trigger.During an insert or update transaction, new rows are added to both the inserted table and the trigger table. Le righe della tabella inserted sono copie delle nuove righe della tabella di trigger.The rows in the inserted table are copies of the new rows in the trigger table.

    Una transazione UPDATE corrisponde a un'eliminazione seguita da un inserimento. Per prima cosa le righe precedenti vengono copiate nella tabella deleted e quindi le nuove righe vengono copiate nella tabella inserted.An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

    Quando si impostano le condizioni di trigger, utilizzare correttamente le tabelle inserted e deleted in base all'azione che ha attivato il trigger.When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the 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.Although referencing the deleted table when testing an INSERT or the inserted table when testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

Nota

Se le azioni dei trigger dipendono dal numero di righe interessate dalle modifiche apportate ai dati, usare le verifiche (ad esempio un esame di @@ROWCOUNT) nel caso di modifiche apportate ai dati di più righe (un'istruzione INSERT, DELETE o UPDATE basata su un'istruzione SELECT) ed eseguire le azioni appropriate.If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

SQL Server 2017SQL Server 2017 non è possibile usare i riferimenti di colonna di tipo text, ntexto image nelle tabelle inserted e deleted per i trigger AFTER. does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. Questi tipi di dati sono tuttavia disponibili per garantire la compatibilità con le versioni precedenti.However, these data types are included for backward compatibility purposes only. L'archiviazione preferita per i dati di grandi dimensioni consiste nell'usare i tipi di dati varchar(max), nvarchar(max)e varbinary(max) .The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. I trigger AFTER e INSTEAD OF supportano i dati varchar(max), nvarchar(max) e varbinary(max) nelle tabelle inserted e deleted.Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. Per altre informazioni, vedere CREATE TRIGGER (Transact-SQL).For more information, see CREATE TRIGGER (Transact-SQL).

Esempio di utilizzo della tabella inserted in un trigger per l'applicazione delle regole businessAn Example of Using the inserted Table in a Trigger to Enforce Business Rules

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.Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

Nell'esempio seguente viene creato un trigger DML.The following example creates a DML trigger. 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 .This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. 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.To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. Se la posizione creditizia è troppo bassa, viene visualizzato un messaggio e l'operazione di inserimento non viene eseguita.If the credit rating is too low, a message is displayed and the insertion does not execute. Si noti che questo esempio non consente modifiche ai dati a riga multipla.Note that this example does not allow for multirow data modifications. Per altre informazioni, vedere Creazione di trigger DML per gestire più righe di dati.For more information, see Create DML Triggers to Handle Multiple Rows of Data.

USE AdventureWorks2012;
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 EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.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

Utilizzo delle tabelle inserted e deleted nei trigger INSTEAD OFUsing the inserted and deleted Tables in INSTEAD OF Triggers

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.The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. Il formato delle tabelle inserted e deleted è uguale a quello della tabella in cui è stato definito il trigger INSTEAD OF.The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Ogni colonna delle tabelle inserted e deleted è mappata direttamente a una colonna della tabella di base.Each column in the inserted and deleted tables maps directly to a column in the base table.

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:The following rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

  • Non è possibile specificare valori per colonne calcolate o colonne con tipo di dati timestamp .Values cannot be specified for computed columns or columns with a timestamp data type.

  • Non è possibile specificare valori per colonne con proprietà IDENTITY, a meno che IDENTITY_INSERT non sia impostata su ON per la tabella.Values cannot be specified for columns with an IDENTITY property, unless IDENTITY_INSERT is ON for that table. Se IDENTITY_INSERT è impostata su ON, le istruzioni INSERT devono fornire un valore.When IDENTITY_INSERT is ON, INSERT statements must supply a value.

  • Le istruzioni INSERT devono fornire valori per tutte le colonne NOT NULL a cui sono applicati vincoli DEFAULT.INSERT statements must supply values for all NOT NULL columns that do not have DEFAULT constraints.

  • 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.For any columns except computed, identity, or timestamp columns, values are optional for any column that allows nulls, or any NOT NULL column that has a DEFAULT definition.

    Quando un'istruzione INSERT, UPDATE o DELETE fa riferimento a una vista con trigger INSTEAD OF, il Motore di databaseDatabase Engine esegue una chiamata al trigger invece di eseguire azioni sulle tabelle.When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the Motore di databaseDatabase Engine calls the trigger instead of taking any direct action against any table. 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.The trigger must use the information presented in the inserted and deleted tables to build any statements required to implement the requested action in the base tables, even when the format of the information in the inserted and deleted tables built for the view is different from the format of the data in the base tables.

    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.The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. Esempio:For example:

USE AdventureWorks2012;  
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 .The result set for this view has three columns: an int column and two nvarchar columns. 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.The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named BusinessEntityID, an nvarchar column named LName, and an nvarchar column named 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.The select list of a view can also contain expressions that do not directly map to a single base-table column. È possibile che alcune espressioni di vista, ad esempio una chiamata di funzione o costante, non facciano riferimento ad alcuna colonna e vengano pertanto ignorate.Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. Le espressioni complesse possono fare riferimento a più colonne, ma nelle tabelle inserted e deleted è disponibile un solo valore per ogni riga inserita.Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. Lo stesso vale per le espressioni semplici di una vista che fanno riferimento a una colonna calcolata con un'espressione complessa.The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. Un trigger INSTEAD OF nella vista deve essere in grado di gestire questo tipo di espressioni.An INSTEAD OF trigger on the view must handle these types of expressions.