Använd de infogade och borttagna tabellerna

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

DML-utlösarinstruktioner använder två specialtabeller: borttagna och infogade tabeller. SQL Server skapar och hanterar tabellerna automatiskt. Du kan använda dessa tillfälliga, minnesbaserade tabeller för att testa effekterna av vissa dataändringar och för att ange villkor för DML-utlösaråtgärder. Du kan inte direkt ändra data i tabellerna eller utföra DDL-åtgärder (datadefinitionsspråk) i tabellerna, till exempel CREATE INDEX.

Förstå de infogade och borttagna tabellerna

I DML-utlösare används de infogade och borttagna tabellerna främst för att utföra följande:

• Utöka referensintegriteten mellan tabeller.

• Infoga eller uppdatera data i bastabeller som ligger bakom en vy.

• Testa för fel och vidta åtgärder baserat på felet.

• Hitta skillnaden mellan tillståndet för en tabell före och efter en dataändring och vidta åtgärder baserat på den skillnaden.

Den borttagna tabellen lagrar kopior av de berörda raderna i utlösartabellen innan de ändrades av en DELETE- eller UPDATE-instruktion (utlösartabellen är den tabell där DML-utlösaren körs). Under körningen av en DELETE- eller UPDATE-instruktion kopieras de berörda raderna först från utlösartabellen och överförs till den borttagna tabellen.

Den -tabellen som infogas lagrar kopior av de nya eller ändrade raderna efter en INSERT- eller UPDATE-sats. Under körningen av en INSERT- eller UPDATE-instruktion kopieras de nya eller ändrade raderna i utlösartabellen till den infogade tabellen. Raderna i den infogade tabellen är kopior av de nya eller uppdaterade raderna i utlösartabellen.

En uppdateringstransaktion liknar en borttagningsåtgärd följt av en infogningsåtgärd. Vid utförandet av en UPDATE-instruktion inträffar följande händelsesekvens:

1. Den ursprungliga raden kopieras från utlösartabellen till den borttagna tabellen.
2. Utlösartabellen uppdateras med de nya värdena från UPDATE-instruktionen.
3. Den uppdaterade raden i utlösartabellen kopieras till den infogade tabellen.

På så sätt kan du jämföra innehållet i raden före uppdateringen (i den borttagna tabellen) med de nya radvärdena efter uppdateringen (i den infogade tabellen).

När du anger utlösarvillkor använder du de infogade och borttagna tabellerna på lämpligt sätt för den åtgärd som utlöste utlösaren. Även om hänvisning till den borttagna tabellen när du testar en INSERT eller den infogade tabellen när du testar en DELETE inte orsakar några fel, innehåller dessa testtabeller för utlösare inga rader i dessa fall.

SQL Server tillåter inte text, ntexteller image kolumnreferenser i de införda och raderade tabellerna för AFTER-utlösare. Dessa datatyper ingår dock endast i bakåtkompatibilitetssyfte. Det bästa lagringsutrymmet för stora data är att använda varchar(max), nvarchar(max)och varbinary(max) datatyper. Både AFTER- och INSTEAD OF-utlösare stöder varchar(max), nvarchar(max)och varbinary(max) data i de infogade och borttagna tabellerna. Mer information finns i CREATE TRIGGER (Transact-SQL).

Exempel: Använd den infogade tabellen i en utlösare för att framtvinga affärsregler

Eftersom CHECK-begränsningar endast kan referera till de kolumner där villkoret på kolumnnivå eller tabellnivå definieras, måste eventuella begränsningar mellan tabeller (i det här fallet affärsregler) definieras som utlösare.

I följande exempel skapas en DML-utlösare. Den här utlösaren kontrollerar att kreditbetyget för leverantören är bra när ett försök görs att infoga en ny inköpsorder i tabellen PurchaseOrderHeader. För att få kreditbetyget för leverantören som motsvarar inköpsordern som just infogades måste den Vendor tabellen refereras till och kopplas till den infogade tabellen. Om kreditbetyget är för lågt visas ett meddelande och insättningen körs inte.

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

Använd de infogade och borttagna tabellerna i STÄLLET för utlösare

De infogade och borttagna tabeller som skickas till INSTEAD OF-utlösare som definierats på tabeller följer samma regler som de infogade och borttagna tabeller som skickas till AFTER-utlösare. Formatet för de infogade och borttagna tabellerna är detsamma som formatet för tabellen där INSTEAD OF-triggern är definierad. Varje kolumn i de infogade och borttagna tabellerna mappar direkt till en kolumn i bastabellen.

Följande regler för när en INSERT- eller UPDATE-instruktion som refererar till en tabell med en I stället för utlösare måste ange värden för kolumner är desamma som om tabellen inte hade någon I stället för utlösare:

• Det går inte att ange värden för beräknade kolumner eller kolumner med en tidsstämpel datatyp.

• Det går inte att ange värden för kolumner med en identitetsegenskap, såvida inte IDENTITY_INSERT är PÅ för den tabellen. När IDENTITY_INSERT är PÅ måste INSERT-instruktioner ange ett värde.

• INSERT-instruktioner måste ange värden för alla NOT NULL-kolumner som inte har standardbegränsningar.

• För alla kolumner utom beräknade kolumner, identiteter eller tidsstämpel kolumner är värden valfria för alla kolumner som tillåter null-värden eller någon NOT NULL-kolumn som har en STANDARDdefinition.

När en INSERT-, UPDATE- eller DELETE-instruktion refererar till en vy som har en INSTEAD OF-utlösare anropar databasmotorn utlösaren istället för att vidta några direkta åtgärder mot en tabell. Utlösaren måste använda informationen som visas i de infogade och borttagna tabellerna för att skapa de instruktioner som krävs för att implementera den begärda åtgärden i bastabellerna, även om formatet för informationen i de infogade och borttagna tabellerna som skapats för vyn skiljer sig från formatet för data i bastabellerna.

Formatet för de infogade och borttagna tabellerna som skickas till en I STÄLLET FÖR-utlösare som definierats i en vy matchar urvalslistan för SELECT-instruktionen som definierats för vyn. Till exempel:

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;  

Resultatuppsättningen för den här vyn har tre kolumner: en int kolumn och två nvarchar kolumner. De infogade och borttagna tabellerna som skickas till en I STÄLLET FÖR-utlösare som definierats i vyn har också en int-kolumn med namnet BusinessEntityID, en nvarchar kolumn med namnet LNameoch en nvarchar kolumn med namnet FName.

Den valda listan över en vy kan också innehålla uttryck som inte mappas direkt till en enda bastabellkolumn. Vissa vyuttryck, till exempel en konstant eller ett funktionsanrop, kanske inte refererar till några kolumner och kan bortses från. Komplexa uttryck kan referera till flera kolumner, men de infogade och borttagna tabellerna har bara ett värde för varje infogad rad. Samma problem gäller för enkla uttryck i en vy om de refererar till en beräknad kolumn som har ett komplext uttryck. En Instead-of-utlösare på vyn måste hantera dessa typer av uttryck.

Prestandaöverväganden

Eftersom de infogade och borttagna tabellerna är virtuella, minnesbaserade tabeller är egenskaper som statistik eller index inte tillgängliga. Även om viss kardinalitetsinformation exponeras från dessa tabeller bör du vara försiktig när du överväger hur många rader som tillfälligt ska lagras där. Om du infogar ett stort antal rader i de här tabellerna och ansluter dem till andra tabeller eller utför sökningar kan det leda till ineffektiva frågeplaner och långsamma sökningar. Se till att noggrant utforma och testa ditt program för att uppfylla dina frågeprestandabehov.

Nästa steg

Mer information finns i översikten över DML-utlösare.