De ingevoegde en verwijderde tabellen gebruiken

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

DML-triggerinstructies maken gebruik van twee speciale tabellen: de verwijderde en ingevoegde tabellen. Met SQL Server worden deze tabellen automatisch gemaakt en beheerd. U kunt deze tijdelijke tabellen in het geheugen gebruiken om de effecten van bepaalde gegevenswijzigingen te testen en voorwaarden in te stellen voor DML-triggeracties. U kunt de gegevens in de tabellen niet rechtstreeks wijzigen of DDL-bewerkingen (Data Definition Language) uitvoeren op de tabellen, zoals CREATE INDEX.

Informatie over de ingevoegde en verwijderde tabellen

In DML-triggers worden de ingevoegde en verwijderde tabellen voornamelijk gebruikt om het volgende uit te voeren:

• Verbreed referentiële integriteit tussen tabellen.

• Gegevens invoegen of bijwerken in basistabellen onder een weergave.

• Test op fouten en neem actie op basis van de fout.

• Zoek het verschil tussen de status van een tabel vóór en na een gegevenswijziging en voer acties uit op basis van dat verschil.

De verwijderd tabel bevat kopieën van de betrokken rijen in de triggertabel voordat ze zijn gewijzigd met een DELETE- of UPDATE-instructie (de triggertabel is de tabel waarop de DML-trigger wordt uitgevoerd). Tijdens de uitvoering van een DELETE- of UPDATE-instructie worden de betrokken rijen eerst gekopieerd uit de triggertabel en overgebracht naar de verwijderde tabel.

In de ingevoegde tabel worden kopieën van de nieuwe of gewijzigde rijen opgeslagen na een INSERT- of UPDATE-instructie. Tijdens de uitvoering van een INSERT- of UPDATE-instructie worden de nieuwe of gewijzigde rijen in de triggertabel gekopieerd naar de ingevoegde tabel. De rijen in de ingevoegde tabel zijn kopieën van de nieuwe of bijgewerkte rijen in de triggertabel.

Een updatetransactie is vergelijkbaar met een verwijderbewerking, gevolgd door een invoegbewerking. Tijdens de uitvoering van een UPDATE-instructie vindt de volgende reeks gebeurtenissen plaats:

1. De oorspronkelijke rij wordt gekopieerd van de triggertabel naar de verwijderde tabel.
2. De triggertabel wordt bijgewerkt met de nieuwe waarden uit de UPDATE-opdracht.
3. De bijgewerkte rij in de triggertabel wordt gekopieerd naar de ingevoegde tabel.

Hiermee kunt u de inhoud van de rij vergelijken vóór de update (in de verwijderde tabel) met de nieuwe rijwaarden na de update (in de ingevoegde tabel).

Wanneer u triggervoorwaarden instelt, gebruikt u de ingevoegde en verwijderde tabellen op de juiste manier voor de actie die de trigger heeft geactiveerd. Hoewel bij het testen van een INSERT naar de verwijderde tabel verwijzen of bij het testen van een DELETE naar de ingevoegde tabel verwijzen geen fouten veroorzaakt, bevatten deze testtabellen in deze gevallen geen rijen.

Notitie

Als triggeracties afhankelijk zijn van het aantal rijen dat door een gegevenswijziging wordt beïnvloed, gebruikt u tests (zoals het onderzoeken van @@ROWCOUNT) voor wijzigingen die meerdere rijen betreffen (zoals een INSERT, DELETE of UPDATE op basis van een SELECT-instructie) en voert u de juiste acties uit. Zie DML-triggers maken voor het afhandelen van meerdere rijen met gegevensvoor meer informatie.

SQL Server staat geen tekst-, ntext-of afbeeldingskolomverwijzingen toe in de ingevoegde en verwijderde tabellen voor AFTER-triggers. Deze gegevenstypen zijn echter alleen opgenomen voor achterwaartse compatibiliteitsdoeleinden. De voorkeursopslag voor grote gegevens is het gebruik van de varchar(max), nvarchar(max)en varbinary(max) gegevenstypen. Zowel AFTER als INSTEAD OF triggers ondersteunen varchar(max), nvarchar(max)en varbinary(max) gegevens in de ingevoegde en verwijderde tabellen. Zie CREATE TRIGGER (Transact-SQL)voor meer informatie.

Voorbeeld: De ingevoegde tabel in een trigger gebruiken om bedrijfsregels af te dwingen

Omdat CHECK-beperkingen alleen kunnen verwijzen naar de kolommen waarop de beperking op kolomniveau of tabelniveau is gedefinieerd, moeten eventuele beperkingen voor meerdere tabellen (in dit geval bedrijfsregels) worden gedefinieerd als triggers.

In het volgende voorbeeld wordt een DML-trigger gemaakt. Met deze trigger wordt gecontroleerd of de rating voor de leverancier goed is wanneer een poging wordt gedaan om een nieuwe inkooporder in te voegen in de PurchaseOrderHeader tabel. Als u de kredietclassificatie van de leverancier wilt verkrijgen die overeenkomt met de inkooporder die zojuist is ingevoegd, moet naar de Vendor tabel worden verwezen en gekoppeld aan de ingevoegde tabel. Als de rating te laag is, wordt een bericht weergegeven en wordt de invoeging niet uitgevoerd.

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

De ingevoegde en verwijderde tabellen gebruiken in PLAATS VAN triggers

De ingevoegde en verwijderde tabellen die worden doorgegeven aan INSTEAD OF-triggers die in tabellen zijn gedefinieerd, volgen dezelfde regels als de ingevoegde en verwijderde tabellen die worden doorgegeven aan AFTER-triggers. De indeling van de ingevoegde en verwijderde tabellen is hetzelfde als de indeling van de tabel waarop de INSTEAD OF-trigger is gedefinieerd. Elke kolom in de ingevoegde en verwijderde tabellen wordt rechtstreeks toegewezen aan een kolom in de basistabel.

De volgende regels met betrekking tot wanneer een INSERT- of UPDATE-instructie verwijst naar een tabel met een INSTEAD OF-trigger, moeten waarden voor kolommen opgeven alsof de tabel geen INSTEAD OF-trigger heeft:

• Waarden kunnen niet worden opgegeven voor berekende kolommen of kolommen met een tijdstempel gegevenstype.

• Waarden kunnen niet worden opgegeven voor kolommen met een eigenschap IDENTITY, tenzij IDENTITY_INSERT is ingeschakeld voor die tabel. Wanneer IDENTITY_INSERT ON is, moeten INSERT-instructies een waarde opgeven.

• INSERT-instructies moeten waarden opgeven voor alle NIET NULL-kolommen waarvoor geen STANDAARDbeperkingen gelden.

• Voor kolommen, met uitzondering van berekende kolommen, identiteiten of tijdstempels kolommen, zijn waarden optioneel voor elke kolom die null-waarden toestaat of een NOT NULL-kolom met een STANDAARDdefinitie.

Wanneer een INSERT-, UPDATE- of DELETE-instructie verwijst naar een weergave met een INSTEAD OF-trigger, roept de Database Engine de trigger aan in plaats van een directe actie uit te voeren op een tabel. De trigger moet de informatie in de ingevoegde en verwijderde tabellen gebruiken om eventuele instructies te maken die nodig zijn om de aangevraagde actie in de basistabellen te implementeren, zelfs wanneer de indeling van de informatie in de ingevoegde en verwijderde tabellen die voor de weergave zijn gemaakt, verschilt van de indeling van de gegevens in de basistabellen.

De indeling van de ingevoegde en verwijderde tabellen die zijn doorgegeven aan een "IN PLAATS VAN" trigger, gedefinieerd in een weergave, komt overeen met de selectielijst van de SELECT-instructie die voor de weergave is gedefinieerd. Bijvoorbeeld:

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;  

De resultatenset voor deze weergave heeft drie kolommen: een int kolom en twee nvarchar kolommen. De ingevoegde en verwijderde tabellen die zijn doorgegeven aan een IN PLAATS VAN-trigger die op de weergave is gedefinieerd, hebben ook een int kolom genaamd BusinessEntityID, een nvarchar kolom genaamd LName, en een nvarchar kolom genaamd FName.

De selectielijst van een weergave kan ook expressies bevatten die niet rechtstreeks aan een kolom van een basistabel worden toegewezen. Sommige weergave-expressies, zoals een constante of functieaanroep, verwijzen mogelijk niet naar kolommen en kunnen worden genegeerd. Complexe expressies kunnen verwijzen naar meerdere kolommen, maar de ingevoegde en verwijderde tabellen hebben slechts één waarde voor elke ingevoegde rij. Dezelfde problemen zijn van toepassing op eenvoudige expressies in een weergave als ze verwijzen naar een berekende kolom met een complexe expressie. Een INSTEAD OF trigger op de weergave moet deze typen van expressies verwerken.

Prestatieoverwegingen

Omdat de ingevoegde en verwijderde tabellen virtuele tabellen zijn, zijn er geen eigenschappen zoals statistieken of indexen beschikbaar. Hoewel bepaalde kardinaliteitsgegevens uit deze tabellen worden weergegeven, moet u voorzichtig zijn bij het overwegen van het aantal rijen dat daar tijdelijk moet worden opgeslagen. Het invoegen van een groot aantal rijen in deze tabellen en het uitvoeren van query's of samenvoegen met andere tabellen kan leiden tot suboptimale queryplannen en trage queryuitvoeringen. Zorg ervoor dat u uw toepassing zorgvuldig ontwerpt en test om te voldoen aan de behoeften van uw queryprestaties.

Volgende stappen

Zie het overzicht van DML-triggersvoor meer informatie.