Używanie wstawionych i usuniętych tabel

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric

Instrukcje wyzwalacza DML używają dwóch specjalnych tabel: usuniętych i wstawionych tabel. Program SQL Server automatycznie tworzy te tabele i zarządza nimi. Możesz użyć tych tymczasowych, rezydentnych tabel pamięci, aby przetestować skutki niektórych modyfikacji danych i ustawić warunki dla akcji wyzwalacza DML. Nie można bezpośrednio modyfikować danych w tabelach ani wykonywać operacji języka definicji danych (DDL) na tabelach, takich jak CREATE INDEX.

Informacje o wstawionych i usuniętych tabelach

W wyzwalaczach DML tabele wstawione i usunięte są używane głównie do wykonywania następujących czynności:

• Rozszerz integralność referencyjną między tabelami.

• Wstawianie lub aktualizowanie danych w tabelach bazowych w widoku.

• Przetestuj błędy i podejmij działania na podstawie błędu.

• Znajdź różnicę między stanem tabeli przed i po modyfikacji danych i podejmij działania na podstawie tej różnicy.

Usunięta tabela przechowuje kopie dotkniętych wierszy w tabeli wyzwalającej przed ich zmianą przez DELETE polecenie lub UPDATE (tabela wyzwalająca to ta, na której uruchamiany jest wyzwalacz DML). Podczas wykonywania DELETE instrukcji or UPDATE zakażone wiersze są najpierw kopiowane z tabeli wyzwalającej i przekazywane do usuniętej tabeli.

Wstawiona tabela przechowuje kopie nowych lub zmienionych wierszy po INSERT instrukcji or.UPDATE Podczas wykonywania INSERT instrukcji or UPDATE nowe lub zmienione wiersze w tabeli wyzwalającej są kopiowane do wstawionej tabeli. Wiersze w wstawionej tabeli to kopie nowych lub zaktualizowanych wierszy w tabeli wyzwalacza.

Transakcja aktualizacji jest podobna do operacji usuwania, po której następuje operacja wstawiania. Podczas wykonywania UPDATE instrukcji następuje następująca sekwencja zdarzeń:

1. Oryginalny wiersz jest kopiowany z tabeli wyzwalacza do usuniętej tabeli.
2. Tabela wyzwalaczy jest aktualizowana o nowe wartości z UPDATE polecenia.
3. Zaktualizowany wiersz w tabeli wyzwalacza jest kopiowany do wstawionej tabeli.

Dzięki temu można porównać zawartość wiersza przed aktualizacją (w usuniętej tabeli) z nowymi wartościami wierszy po aktualizacji (w wstawionej tabeli).

Po ustawieniu warunków wyzwalacza należy odpowiednio użyć wstawionych i usuniętych tabel dla akcji, która wyzwoliła wyzwalacz. Chociaż odwołanie się do usuniętej tabeli podczas testowania lub INSERT wstawionej tabeli podczas testowania nie DELETE powoduje błędów, te tabele testów wyzwalających nie zawierają żadnych wierszy w tych przypadkach.

Program SQL Server nie zezwala na tekstu, ntextlub odwołania do kolumn obrazu w wstawionych i usuniętych tabelach wyzwalaczy AFTER. Jednak te typy danych są uwzględniane tylko w celach zgodności z poprzednimi wersjami. Preferowanym magazynem dla dużych danych jest użycie varchar(max), nvarchar(max)i varbinary(max) typów danych. Zarówno wyzwalacze AFTER, jak i INSTEAD OF obsługują dane typu varchar(max), nvarchar(max) i varbinary(max) w tabelach wstawionych i usuniętych. Aby uzyskać więcej informacji, zobacz CREATE TRIGGER (Transact-SQL).

Przykład: używanie wstawionej tabeli w wyzwalaczu w celu wymuszania reguł biznesowych

Ponieważ ograniczenia CHECK mogą odwoływać się tylko do kolumn, na których zdefiniowano ograniczenie na poziomie kolumny lub na poziomie tabeli, wszystkie ograniczenia między tabelami (w tym przypadku reguły biznesowe) muszą być zdefiniowane jako wyzwalacze.

Poniższy przykład tworzy wyzwalacz DML. Ten wyzwalacz sprawdza, czy ocena kredytowa dostawcy jest dobra podczas próby wstawienia nowego zamówienia zakupu do tabeli PurchaseOrderHeader. Aby uzyskać ocenę kredytową dostawcy odpowiadającą właśnie wstawionemu zamówieniu zakupu, należy odwołać się do tabeli Vendor i połączyć z wstawioną tabelą. Jeśli ocena kredytowa jest zbyt niska, zostanie wyświetlony komunikat i wstawienie nie zostanie wykonane.

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

Używanie wstawionych i usuniętych tabel zamiast wyzwalaczy

Wstawiane i usunięte tabele, które są przekazywane do wyzwalaczy INSTEAD OF zdefiniowanych na tabelach, podlegają tym samym regułom co wstawiane i usunięte tabele przekazywane do wyzwalaczy AFTER. Format wstawionych i usuniętych tabel jest taki sam jak format tabeli, na której zdefiniowano wyzwalacz INSTEAD OF. Każda kolumna w wstawionych i usuniętych tabelach jest mapowana bezpośrednio na kolumnę w tabeli bazowej.

Następujące zasady dotyczące sytuacji, gdy INSERT instrukcja or UPDATE odnosząca się do tabeli z wyzwalaczem INSTEAD OF musi dostarczać wartości kolumn, są takie same, jakby tabela nie miała wyzwalacza INSTEAD OF:

• Nie można określić wartości dla obliczonych kolumn lub kolumn zawierających typ danych timestamp .

• Wartości nie mogą być określone dla kolumn z właściwością IDENTITY , chyba że IDENTITY_INSERT dla tej tabeli jest ON. Gdy IDENTITY_INSERT jest ON, INSERT instrukcje muszą dostarczyć wartość.

• INSERT instrukcje muszą dostarczać wartości dla wszystkich kolumn NOT NULL, które nie mają DEFAULT ograniczeń.

• Dla wszystkich kolumn z wyjątkiem kolumn obliczeniowych, tożsamościowych lub znaczników czasu , wartości są opcjonalne dla każdej kolumny, która dopuszcza null, lub dla każdej kolumny NOT NULL z definicją DEFAULT .

Gdy INSERTinstrukcja , UPDATE, lub DELETE odnosi się do widoku, który ma wyzwalacz INSTEAD OF, Database Engine wywołuje wyzwalacz zamiast podejmować bezpośrednie działanie przeciwko jakiejkolwiek tabeli. Wyzwalacz musi używać informacji przedstawionych w wstawionych i usuniętych tabelach, aby utworzyć instrukcje wymagane do zaimplementowania żądanej akcji w tabelach podstawowych, nawet jeśli format informacji w wstawionych i usuniętych tabelach utworzonych dla widoku różni się od formatu danych w tabelach bazowych.

Format wstawionych i usuniętych tabel przekazanych do wyzwalacza INSTEAD OF zdefiniowanego na widoku jest zgodny z listą wyboru instrukcji SELECT zdefiniowanej dla tego widoku. Na przykład:

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;  

Zestaw wyników dla tego widoku zawiera trzy kolumny: jedną kolumnę typu int i dwie kolumny typu nvarchar . Wstawione i usunięte tabele przekazane do wyzwalacza ZAMIAST zdefiniowanego w widoku mają również kolumnę int o nazwie BusinessEntityID, kolumnę nvarchar o nazwie LName oraz kolumnę nvarchar o nazwie FName.

Lista wyboru w widoku może również zawierać wyrażenia, które nie są bezpośrednio mapowane na pojedynczą kolumnę tabeli bazowej. Niektóre wyrażenia widoku, takie jak wywołanie stałe lub funkcji, mogą nie odwoływać się do żadnych kolumn i mogą być ignorowane. Wyrażenia złożone mogą odwoływać się do wielu kolumn, ale wstawione i usunięte tabele mają tylko jedną wartość dla każdego wstawionego wiersza. Te same problemy dotyczą wyrażeń prostych w widoku, jeśli odwołują się do obliczonej kolumny zawierającej złożone wyrażenie. Wyzwalacz INSTEAD OF na widoku musi obsługiwać te typy wyrażeń.

Zagadnienia dotyczące wydajności

Ponieważ wstawiane i usunięte tabele są tabelami wirtualnymi, tabele rezydentne pamięci, takie jak statystyki lub indeksy, nie są dostępne. Chociaż niektóre informacje o kardynalności są widoczne w tych tabelach, należy zachować ostrożność, biorąc pod uwagę liczbę wierszy, które mają być tymczasowo tam przechowywane. Wstawianie dużej liczby wierszy w tych tabelach oraz wykonywanie zapytań lub łączenie ich z innymi tabelami może prowadzić do nieoptymalnych planów zapytań i spowolnienia ich wykonywania. Pamiętaj, aby dokładnie zaprojektować i przetestować aplikację, aby spełnić wymagania dotyczące wydajności zapytań.

Treści powiązane

• Wyzwalacze DML