Używanie wstawionych i usuniętych tabel

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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) w 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.

Tabela usunięta przechowuje kopie wierszy, których dotyczy problem, przed ich zmianą przez instrukcję DELETE lub UPDATE (tabela wyzwalacza jest tabelą, w której jest uruchamiany wyzwalacz DML). Podczas wykonywania instrukcji DELETE lub UPDATE wiersze, których dotyczy problem, są najpierw kopiowane z tabeli wyzwalacza i przenoszone do usuniętej tabeli.

Tabela wstawiona przechowuje kopie tych nowych lub zmienionych wierszy jako wynik działania instrukcji INSERT lub UPDATE. Podczas wykonywania instrukcji INSERT lub UPDATE nowe lub zmienione wiersze w tabeli wyzwalacza 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 instrukcji UPDATE następuje następująca sekwencja zdarzeń:

1. Oryginalny wiersz jest kopiowany z tabeli wyzwalacza do usuniętej tabeli.
2. Tabela wyzwalacza jest aktualizowana przy użyciu nowych wartości z instrukcji UPDATE.
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. Mimo że odwoływanie się do usuniętej tabeli podczas testowania instrukcji INSERT lub wstawionej tabeli podczas testowania funkcji DELETE nie powoduje żadnych błędów, te tabele testowe wyzwalacza 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 w wyzwalaczach PO, jak i ZAMIAST obsługiwane są 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   
           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

Używanie wstawionych i usuniętych tabel zamiast wyzwalaczy

Wstawiane i usunięte tabele przekazywane do wyzwalaczy INSTEAD OF zdefiniowanych na tabelach są zgodne z tymi samymi regułami, 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ą sytuacji, gdy instrukcja INSERT lub UPDATE dotycząca tabeli z wyzwalaczem INSTEAD OF musi dostarczyć wartości dla kolumn — są one 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 .

• Nie można określić wartości dla kolumn z właściwością IDENTITY, chyba że IDENTITY_INSERT jest włączona dla tej tabeli. Gdy IDENTITY_INSERT jest włączona, instrukcje INSERT muszą podać wartość.

• Instrukcje INSERT muszą podawać wartości dla wszystkich kolumn NOT NULL, które nie mają ograniczeń DOMYŚLNYch.

• W przypadku kolumn z wyjątkiem kolumn obliczeniowych, identyfikatorów albo kolumn sygnatury czasowej , wartości są opcjonalne dla dowolnej kolumny, która zezwala na wartości null, lub dowolnej kolumny z ograniczeniem NOT NULL, która ma zdefiniowaną wartość domyślną.

Gdy instrukcja INSERT, UPDATE lub DELETE odwołuje się do widoku, który ma wyzwalacz INSTEAD OF, silnik bazy danych wywołuje ten wyzwalacz zamiast podejmować jakieś bezpośrednie działania na dowolnej 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 wyzwalacza zdefiniowanego w widoku mają również kolumnę int o nazwie BusinessEntityID, kolumnę nvarchar o nazwie LNameoraz 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ń.

Następne kroki

Aby uzyskać więcej informacji, zobacz omówienie wyzwalaczy DML .