Použití vložených a odstraněných tabulek

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Příkazy triggeru DML používají dvě speciální tabulky: odstraněné a vložené tabulky. SQL Server tyto tabulky automaticky vytvoří a spravuje. Tyto dočasné tabulky s rezidenty paměti můžete použít k otestování účinků určitých úprav dat a nastavení podmínek pro akce triggeru DML. Data v tabulkách nelze přímo upravovat ani provádět operace jazyka DDL (Data Definition Language) u tabulek, jako je například CREATE INDEX.

Pochopení vložených a odstraněných tabulek

V triggerech DML se vložené a odstraněné tabulky primárně používají k provedení následujících akcí:

• Rozšiřte referenční integritu mezi tabulkami.

• Vloží nebo aktualizuje data v základních tabulkách, které tvoří podkladové zobrazení.

• Otestujte chyby a proveďte akci na základě této chyby.

• Najděte rozdíl mezi stavem tabulky před a po úpravě dat a proveďte akce na základě tohoto rozdílu.

Odstraněná tabulka ukládá kopie ovlivněných řádků v tabulce triggeru předtím, než byly změněny příkazem DELETE nebo UPDATE (tabulka triggerů je tabulka, na které se spouští trigger DML). Během provádění příkazu DELETE nebo UPDATE se ovlivněné řádky nejprve zkopírují z tabulky triggerů a přenesou se do odstraněné tabulky.

Vložená tabulka ukládá kopie nových nebo změněných řádků za příkaz INSERT nebo UPDATE. Během provádění příkazu INSERT nebo UPDATE se nové nebo změněné řádky v tabulce triggeru zkopírují do vložené tabulky. Řádky v vložené tabulce jsou kopiemi nových nebo aktualizovaných řádků v tabulce triggeru.

Aktualizační transakce je podobná operaci odstranění následovanou operací vložení. Během provádění příkazu UPDATE dojde k následující sekvenci událostí:

1. Původní řádek se zkopíruje z tabulky triggerů do odstraněné tabulky.
2. Tabulka triggerů se aktualizuje o nové hodnoty z příkazu UPDATE.
3. Aktualizovaný řádek v tabulce triggeru se zkopíruje do vložené tabulky.

To vám umožní porovnat obsah řádku před aktualizací (v odstraněné tabulce) s novými hodnotami řádků po aktualizaci (v vložené tabulce).

Při nastavování podmínek triggeru použijte vložené a odstraněné tabulky odpovídajícím způsobem pro akci, která trigger aktivovala. Přestože odkazování na odstraněnou tabulku při testování INSERT nebo vložené tabulky při testování DELETE nezpůsobuje žádné chyby, tyto testovací tabulky triggeru neobsahují v těchto případech žádné řádky.

Poznámka

Pokud akce triggeru závisí na počtu řádků, na které má úprava dat vliv, použijte testy (například prověření @@ROWCOUNT) při úpravách více řádků (INSERT, DELETE nebo UPDATE na základě příkazu SELECT) a provádějte příslušné akce. Další informace naleznete v tématu Vytvoření triggerů DML pro zpracování více řádků dat.

SQL Server neumožňuje textové, ntextnebo obrázku odkazy na sloupce ve vložených a odstraněných tabulkách pro triggery AFTER. Tyto datové typy jsou však zahrnuty pouze pro účely zpětné kompatibility. Upřednostňovaným úložištěm velkých dat je použití varchar(max), nvarchar(max)a varbinary(max) datových typů. Triggery AFTER i MÍSTO FUNKCE podporují varchar(max), nvarchar(max)a varbinary(max) data v vložených a odstraněných tabulkách. Další informace naleznete v tématu CREATE TRIGGER (Transact-SQL).

Příklad: Použití vložené tabulky v triggeru k vynucení obchodních pravidel

Vzhledem k tomu, že omezení CHECK můžou odkazovat pouze na sloupce, na kterých je definováno omezení na úrovni sloupce nebo tabulky, musí být všechna omezení křížové tabulky (v tomto případě obchodní pravidla) definována jako triggery.

Následující příklad vytvoří trigger DML. Tato aktivační událost zkontroluje, jestli je hodnocení kredibility pro dodavatele dobré, když se pokusíte vložit novou nákupní objednávku do tabulky PurchaseOrderHeader. Chcete-li získat rating dodavatele odpovídající právě vložené nákupní objednávce, musí být tabulka Vendor odkazována a připojena k vložené tabulce. Pokud je hodnocení kreditu příliš nízké, zobrazí se zpráva a vložení se neprovede.

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

Použijte vložené a odstraněné tabulky v INSTEAD OF triggerech

Vložené a odstraněné tabulky předané triggerům INSTEAD OF, definovaným v tabulkách, se řídí stejnými pravidly jako vložené a odstraněné tabulky předané triggerům AFTER. Formát vložených a odstraněných tabulek je stejný jako formát tabulky, na které je definován INSTEAD OF trigger. Každý sloupec v tabulkách vložených a odstraněných se mapuje přímo na sloupec v základní tabulce.

Následující pravidla týkající se toho, kdy příkaz INSERT nebo UPDATE odkazující na tabulku s triggerem INSTEAD OF musí zadat hodnoty pro sloupce, jsou stejná, jako kdyby tabulka neobsahovala trigger INSTEAD OF.

• Hodnoty nelze zadat pro počítané sloupce nebo sloupce s časovým razítkem datový typ.

• Hodnoty nelze zadat pro sloupce s vlastností IDENTITY, pokud IDENTITY_INSERT není pro tuto tabulku ZAPNUTO. Pokud je IDENTITY_INSERT ZAPNUTO, musí příkazy INSERT zadat hodnotu.

• Příkazy INSERT musí zadávat hodnoty pro všechny sloupce NOT NULL, které nemají výchozí omezení.

• Pro všechny sloupce s výjimkou počítaných, identity nebo časových razítek, jsou hodnoty volitelné pro sloupec, který povoluje hodnoty null, nebo pro sloupec NOT NULL, který má výchozí definici.

Když příkaz INSERT, UPDATE nebo DELETE odkazuje na zobrazení, které má trigger typu INSTEAD OF, databázový stroj volá tento trigger místo provedení jakékoli přímé akce se žádnou tabulkou. Trigger musí použít informace uvedené v vložených a odstraněných tabulkách k sestavení všech příkazů potřebných k implementaci požadované akce v základních tabulkách, i když se formát informací v vložených a odstraněných tabulkách vytvořených pro zobrazení liší od formátu dat v základních tabulkách.

Formát vložených a odstraněných tabulek předaných triggeru NAMÍSTO FUNKCE DEFINOVANÉ v zobrazení odpovídá seznamu výběrů příkazu SELECT definovaného pro zobrazení. Například:

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;  

Sada výsledků pro toto zobrazení obsahuje tři sloupce: jeden sloupec int a dva sloupce nvarchar. Vložené a odstraněné tabulky předané triggeru typu INSTEAD OF definovanému na zobrazení mají také sloupec int s názvem BusinessEntityID, sloupec nvarchar s názvem LNamea sloupec nvarchar s názvem FName.

Výběrový seznam pohledu může také obsahovat výrazy, které neodpovídají přímo jednomu sloupci základní tabulky. Některé výrazy zobrazení, například vyvolání konstanty nebo funkce, nemusí odkazovat na žádné sloupce a je možné je ignorovat. Složité výrazy můžou odkazovat na více sloupců, ale vložené a odstraněné tabulky mají pouze jednu hodnotu pro každý vložený řádek. Stejné problémy platí pro jednoduché výrazy v zobrazení, pokud odkazují na počítaný sloupec, který má složitý výraz. INSTEAD OF trigger v zobrazení musí zpracovávat tyto typy výrazů.

Důležité informace o výkonu

Vzhledem k tomu, že vložené a odstraněné tabulky jsou virtuální, nejsou k dispozici vlastnosti rezidentní paměti, jako jsou statistiky nebo indexy. Přestože některé informace o kardinalitě jsou z těchto tabulek přístupné, měli byste dbát opatrnosti při zvažování počtu řádků, které chcete dočasně uložit. Vložení velkého počtu řádků do těchto tabulek a dotazování nebo jejich spojování s jinými tabulkami může vést k neoptimálním plánům dotazů a pomalým spouštěním dotazů. Nezapomeňte pečlivě navrhnout a otestovat aplikaci tak, aby splňovala požadavky na výkon dotazů.

Další kroky

Další informace najdete v přehledu DML triggerů.