Használd a beillesztett és törölt táblázatokat

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

A DML trigger utasítások két speciális táblát használnak: a törölt és beilleszett táblákat. Az SQL Server automatikusan létrehozza és kezeli ezeket a táblákat. Ezeket az ideiglenes, memória-tartó táblákat használhatod bizonyos adatmódosítások hatásainak tesztelésére és a DML trigger műveletek feltételeinek beállítására. Nem lehet közvetlenül módosítani az adatokat a táblákban, vagy adatdefiníciós nyelvi (DDL) műveleteket végezni a táblákon, például CREATE INDEXET.

A beillesztett és törölt táblázatok megértése

A DML triggerekben a beillesztett és törölt táblákat elsősorban a következők végrehajtására használják:

• Terjesítsük a referenciális integritást a táblák között.

• Adat bekerülése vagy frissítése az alaptáblákban, amelyek egy nézet mögött állnak.

• Vizsgáld meg hibákat, és tedd meg a hiba alapján.

• Keresse meg a különbséget egy tábla állapota között az adatmódosítás előtt és után, és tegyen lépéseket ezen a különbség alapján.

A törölt tábla tárolja az érintett sorok másolatait a trigger táblában, mielőtt DELETE vagy UPDATE utasítással módosították volna őket (a trigger table az a táblázat, amelyen a DML trigger fut). A DELETE vagy UPDATE utasítás végrehajtása során az érintett sorokat először a trigger tábláról másolják át, majd áthelyezik a törölt táblára.

A beillesztett tábla tárolja az új vagy megváltoztatott sorok másolatait egy INSERT vagy UPDATE utasítás után. Az INSERT vagy UPDATE utasítás végrehajtása során a trigger tábla új vagy megváltoztatott sorai átmásolódnak a beillesztett táblába. A beillesztett táblázat sorai a trigger táblában szereplő új vagy frissített sorok másolatai.

A frissítési tranzakció hasonló egy törlési művelethez, amelyet egy beillesztési művelet követ. A UPDATE utasítás végrehajtása során a következő eseménysorozat történik:

1. Az eredeti sort a trigger tábláról másolják a törölt táblára.
2. A trigger tábla frissül a UPDATE utasítás új értékeivel.
3. A trigger táblában a frissített sor a beillesztett táblára kerül.

Ez lehetővé teszi, hogy összehasonlítsd a sor tartalmát a frissítés előtti (a törölt táblában) a frissítés utáni új sorértékekkel (a beillesztett táblában).

Amikor beállítod a trigger feltételeket, használd megfelelően a beillesztett és törölt táblázatokat arra az akcióra, amely kilőte a ravaszt. Bár a törölt táblára hivatkozva INSERT teszteléskor vagy a beillesztett táblára DELETE teszteléskor nem okoz hibákat, ezekben az esetekben nem tartalmaznak sorokat.

Megjegyzés:

Ha a trigger műveletek attól függnek, hány sort befolyásol az adatmódosítás, használj teszteket (például @@ROWCOUNT vizsgálatát) többsoros adatmódosításokra (INSERT, DELETE vagy UPDATE egy SELECT utasítás alapján), és tegyél megfelelő lépéseket. További információért lásd: "Létrehozz DML triggereket több sor adatkezelésre".

Az SQL Server nem engedélyezi szöveg-, ntext- vagy képoszlop-hivatkozásokat a beillesztett és törölt táblákban az AFTER triggerekhez. Azonban ezek az adattípusok kizárólag visszafelé kompatibilitás céljából vannak beépítve. A nagy adat preferált tárolása a varchar(max),nvarchar(max) és varbinary(max) adattípusok használata. Mind az AFTER, mind a INSTEAD OF triggerek támogatják a varchar(max), nvarchar(max) és varbinary(max) adatokat a beillesztett és törölt táblázatokban. További információért lásd: LÉTREHOZNI TRIGGER (Transact-SQL).

Példa: Használd a beillesztett táblát egy triggerben az üzleti szabályok érvényesítésére

Mivel a CHECK korlátozások csak azokra az oszlopokra hivatkozhatnak, amelyeken az oszlop- vagy táblázatszintű korlátozás definiálva van definiálva, minden kereszttáblás korlátozást (ebben az esetben üzleti szabályokat) triggerként kell definiálni.

A következő példa létrehoz egy DML triggeret. Ez ellenőrzi az eladó hitelminősítését, amikor megpróbálják új vételi megrendelést beadni a PurchaseOrderHeader táblázatba. Ahhoz, hogy megkapjuk a beszállító hitelminősítését, amely megfelel a beillesztett vételi megrendelésnek, a Vendor táblát meg kell hivatkozni és összekapcsolni a beillesztett táblával. Ha a hitelminősítés túl alacsony, üzenet jelenik meg, és a behelyezés nem hajt végre.

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

Használd a beillesztett és törölt táblákat a INSTEAD OF trigger-ben

A beillesztett és törölt táblák, amelyeket a táblákon definiált HELYETT OF triggerekhez továbbítanak, ugyanazokat a szabályokat követik, mint a beillesztett és törölt táblák, amelyeket a AFTER triggerekre továbbítottak. A beillesztett és törölt táblázatok formátuma megegyezik azzal a táblázat formátumával, amelyen a HELYETTE OF trigger van definiálva. A beillesztett és törölt táblázatok minden oszlopa közvetlenül az alaptáblázat egyik oszlopához kapcsolódik.

A következő szabályok arra vonatkozóan, hogy egy INSERT vagy UPDATE utasításnak, amely egy INSTEAD OF triggerrel rendelkező táblára hivatkozik, hogy értékeket kell megadnia az oszlopokhoz, ugyanazok, mintha a táblának nem lenne INSTEAD OF triggere:

• Az értékek nem határozhatók meg kiszámított oszlopokra vagy időbélyeg-adattípusú oszlopokra.

• Az IDENTITY tulajdonsággal rendelkező oszlopok értékei nem határozhatók meg, kivéve, ha IDENTITY_INSERT az adott táblánál ON. Amikor IDENTITY_INSERT ON, az INSERT utasításoknak értéket kell megadnia.

• Az INSERT utasításokat minden olyan NOT NULL oszlop értéket kell megadnia, amelyeknek nincs ALAPÉRTELMEZETT korlátjuk.

• Bármely oszlop esetén, kivéve a kiszámított, identitás- vagy időbélyeg-oszlopokat, az értékek opcionálisak bármely olyan oszlop esetén, amely nullokat enged, vagy bármely NEM NULL oszlopnál, amelynek ALAPÉRTELMEZETT definíciója van.

Amikor egy INSERT, UPDATE vagy DELETE utasítás egy olyan nézetre hivatkozik, amelynek INSTEAD OF triggerje van, az Database Engine a triggeret hívja a trigger ahelyett, hogy közvetlen intézkedést hajtana végre bármely tábla ellen. A triggernek a beillesztett és törölt táblákban szereplő információkat kell felhasználnia ahhoz, hogy létrehozza azokat az állításokat, amelyek szükségesek az alaptáblákban a kért művelet megvalósításához, még akkor is, ha a nézethez épített beillesztett és törölt táblázatokban szereplő információk formátuma eltér az alaptáblák adatformátumától.

A beillesztett és törölt táblák formátuma, amelyet egy HELYETTE OF triggernek továbbítanak egy nézeten definiált, megegyezik a SELECT utasítás kiválasztási listájával, amelyet a nézethez definiált. Például:

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;  

Ennek a nézetnek az eredményhalmaza három oszlopból áll: egy int oszlopból és két nvarchar oszlopból. A beillesztett és törölt táblák, amelyeket a nézeten definiált INSTEAD OF triggerhez továbbítanak, szintén tartalmaznak egy int oszlopot , BusinessEntityIDegy nvarchar oszlopot , LNameés egy nvarchar oszlopot .FName

A nézet kiválasztólistája tartalmazhat olyan kifejezéseket is, amelyek nem közvetlenül egyetlen alaptáblázat-oszlophoz fordulnak. Néhány nézeti kifejezés, például egy állandó vagy függvény meghívás, nem hivatkozik oszlopokra, és figyelmen kívül hagyhatók. A komplex kifejezések több oszlopra hivatkozhatnak, mégis a beillesztett és törölt tábláknak minden beillesztett sorhoz csak egy értékük van. Ugyanezek a problémák érvényesek az egyszerű kifejezésekre is érvényesek egy nézetben, ha egy összetett kifejezésű kiszámított oszlopra hivatkoznak. A nézetben lévő INSTEAD OF triggernek kezelnie kell ezeket a kifejezéseket.

Teljesítménnyel kapcsolatos szempontok

Mivel a beillesztett és törölt táblák virtuális, memóriában tartó táblák, olyan tulajdonságok, mint a statisztikák vagy indexek, nem állnak rendelkezésre. Bár ezekből a táblázatokból néhány kardinalitási információ feltárul, óvatosnak kell lenni, amikor figyelembe kell venni, hány sort kell ideiglenesen tárolni. Sok sor beillesztése ezekbe a táblákba, és lekérdezések vagy más táblákkal való összekapcsolása aloptimális lekérdezési terveket és lassú lekérdezési végrehajtást eredményezhet. Ügyelj rá, hogy alaposan tervezd meg és teszteld az alkalmazást, hogy megfeleljen a lekérdezési teljesítmény igényeidnek.

Következő lépések

További információért lásd a DML Triggers áttekintését.