DML-eseményindítók létrehozása több adatsor kezeléséhez

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

A DML-eseményindító kódjának megírásakor vegye figyelembe, hogy az eseményindítót kiváltó utasítás egyetlen utasítás lehet, amely több adatsort érint egyetlen sor helyett. Ez a viselkedés gyakori az UPDATE és DELETE eseményindítók esetében, mivel ezek az utasítások gyakran több sort is érintenek. Az INSERT-eseményindítók viselkedése kevésbé gyakori, mert az egyszerű INSERT utasítás csak egyetlen sort ad hozzá. Mivel azonban az INSERT eseményindítót az INSERT INTO (table_name) SELECT utasítással lehet aktiválni, számos sor beszúrása egyetlen eseményindító meghívását okozhatja.

A multirow szempontok különösen fontosak, ha egy DML-eseményindító funkciója az, hogy automatikusan újraszámolja az összesítő értékeket az egyik táblából, és az eredményeket egy másikban tárolja a folyamatban lévő tallózatok esetében.

Jegyzet

Nem javasoljuk a kurzorok használatát az eseményindítókban, mert potenciálisan csökkenthetik a teljesítményt. Több sort érintő eseményindító tervezéséhez használjon sorkészletalapú logikát a kurzorok helyett.

Példák

Az alábbi példákban szereplő DML-eseményindítók úgy vannak kialakítva, hogy egy oszlop futó összegét a AdventureWorks2022 mintaadatbázis egy másik táblájában tárolják.

Egy. Folyamatos összeg tárolása egyetlen sor beszúrásához

A DML-eseményindító első verziója jól működik az egysoros beszúráshoz, amikor egy sornyi adat van betöltve a PurchaseOrderDetail táblába. Az INSERT utasítás aktiválja a DML-eseményindítót, és az új sor betöltődik a beszúrt táblába az eseményindító végrehajtásának időtartamára. A UPDATE utasítás beolvassa a sor LineTotal oszlopértékét, és hozzáadja azt a PurchaseOrderHeader tábla SubTotal oszlopában lévő meglévő értékhez. A WHERE záradék biztosítja, hogy a PurchaseOrderDetail táblázat frissített sora megegyezzen a táblában beszúrt sorának PurchaseOrderID értékével.

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;  

B. Folyamatosan frissített összeg tárolása több- vagy egysoros beszúráshoz

Multirow-beszúrás esetén előfordulhat, hogy az A példában a DML-eseményindító nem működik megfelelően; a hozzárendelési kifejezés jobb oldalán lévő kifejezés az UPDATE utasításban (SubTotal + LineTotal) csak egyetlen érték lehet, nem pedig értéklista. Ezért az eseményindító hatása az, hogy a beszúrt tábla bármely sorából lekéri az értéket, és hozzáadja azt a meglévő SubTotal értékhez a PurchaseOrderHeader táblában egy adott PurchaseOrderID értékhez. Előfordulhat, hogy ez a művelet nem rendelkezik a várt hatással, ha egyetlen PurchaseOrderID érték többször is előfordult a beszúrt táblában.

A PurchaseOrderHeader tábla megfelelő frissítéséhez a triggernek engedélyeznie kell a beszúrt táblában a lehetőséget több sorra. Ezt úgy teheti meg, hogy a SUM függvényt használja, amely kiszámítja az összes LineTotal-et a beszúrt táblázat egy csoportjának sorai esetében, az egyes PurchaseOrderID-ekhez. A SUM függvény egy korrelált részkikérdezésben (zárójelben SELECT utasítás) szerepel. Ez az al-lekérdezés egyetlen értéket ad vissza minden PurchaseOrderID esetében, amely a beszúrt táblában van, és amely egyezik vagy összefügg az PurchaseOrderHeader táblában lévő PurchaseOrderID-mal.

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail2  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted);  

Ez az eseményindító egysoros beszúrásban is megfelelően működik; az LineTotal értékoszlop összege egyetlen sor összege. Azonban ezzel az eseményindítóval a korrelált részlekérdezés és a WHERE záradékban használt IN operátor további feldolgozást igényel az SQL Servertől. Ez egysoros beszúrás esetén szükségtelen.

C. A folyamatos összeg tárolása a beszúrás típusa alapján

Az eseményindítót módosíthatja úgy, hogy az optimális metódust használja a sorok számához. A @@ROWCOUNT függvény például az eseményindító logikájában használható egyetlen és egy többhelyes beszúrás megkülönböztetésére.

-- Trigger valid for multirow and single row inserts  
-- and optimal for single row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail3  
ON Purchasing.PurchaseOrderDetail  
FOR INSERT AS  
IF @@ROWCOUNT = 1  
BEGIN  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID  
END  
ELSE  
BEGIN  
      UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted)  
END;  

Lásd még:

DML-eseményindítók