Tabela deleted to wirtualna tabela dostępna w wyzwalaczu, zawierająca wiersze sprzed operacji usunięcia lub aktualizacji.
Rola tabeli deleted w wyzwalaczu DML
W programie Microsoft SQL Server tabela logiczna deleted jest specjalnym obiektem dostępnym podczas wykonywania wyzwalacza DML, czyli wyzwalacza reagującego na operacje DELETE, UPDATE albo odpowiednio zdefiniowany wyzwalacz typu INSTEAD OF. Nie jest to trwała tabela zapisana w bazie danych i nie można odwołać się do niej z dowolnego zapytania wykonywanego poza kontekstem wyzwalacza. SQL Server udostępnia jej zawartość tylko na czas obsługi konkretnej operacji wywołującej wyzwalacz.
Wiersze znajdujące się w deleted przedstawiają stan danych sprzed usunięcia albo sprzed aktualizacji. W przypadku instrukcji DELETE tabela zawiera rekordy usunięte przez daną instrukcję. Przy instrukcji UPDATE zawiera poprzednie wartości zmienianych wierszy, natomiast nowe wartości są dostępne w odpowiadającej jej tabeli logicznej inserted. Dzięki temu wyzwalacz może porównać stan przed zmianą ze stanem po zmianie, sprawdzić warunki biznesowe albo zapisać informację o modyfikacji.
Różnica między deleted, inserted i rodzajem operacji
Znaczenie tabeli deleted zależy od zdarzenia, które uruchomiło wyzwalacz. Dla DELETE można odczytać z niej wartości kolumn usuwanych rekordów. Dla UPDATE tabela przechowuje wartości sprzed modyfikacji, a tabela inserted przechowuje wartości po modyfikacji. Dla zwykłego INSERT tabela deleted nie zawiera wierszy, ponieważ przed dodaniem rekordu nie istniał jego poprzedni stan; nowe rekordy są wtedy dostępne w inserted.
Obie tabele logiczne mają strukturę odpowiadającą tabeli, na której działa wyzwalacz, w zakresie kolumn dostępnych dla danego mechanizmu. W wyzwalaczu obsługującym aktualizację można więc zestawić kolumny za pomocą identyfikatora rekordu, na przykład porównać deleted.Status z inserted.Status. Samo połączenie powinno opierać się na kluczu głównym lub innym jednoznacznym identyfikatorze. Nie należy zakładać, że kolejność wierszy w którejkolwiek z tych tabel odpowiada kolejności przetwarzania wierszy przez instrukcję.
Wyzwalacz w SQL Server działa dla całej instrukcji, a nie osobno dla każdego wiersza. Jeżeli pojedyncze polecenie DELETE usunie sto rekordów, w deleted znajdzie się zbiór stu wierszy. Z tego powodu kod wyzwalacza powinien być napisany w sposób zbiorowy, z użyciem zapytań operujących na wielu rekordach. Konstrukcja zakładająca, że tabela logiczna zawiera zawsze jeden wiersz, może prowadzić do pominięcia danych, niejednoznacznego przypisania wartości albo błędnego działania przy operacjach masowych.
Odczyt danych historycznych podczas aktualizacji
Najczęstszym zastosowaniem deleted przy operacji UPDATE jest ustalenie, co dokładnie się zmieniło. Przykładowo warunek deleted.Cena <> inserted.Cena wskazuje zmianę ceny, a porównanie wartości tekstowych pozwala wykryć zmianę opisu lub statusu. Przy porównywaniu kolumn dopuszczających wartości NULL trzeba uwzględnić logikę trójwartościową SQL. Zwykły operator różności nie wykryje poprawnie każdej zmiany z NULL na wartość albo z wartości na NULL, dlatego warunek musi rozróżniać także przypadki braku wartości.
W wyzwalaczu typu AFTER DELETE tabela deleted pozwala wykonać działania zależne od danych, które właśnie zostały usunięte. Można na jej podstawie zaktualizować podsumowanie, sprawdzić powiązania lub zarejestrować identyfikatory usuniętych rekordów w tabeli historii. Operacje wykonywane przez taki wyzwalacz należą do tego samego kontekstu transakcyjnego co instrukcja, która go uruchomiła. Jeżeli wyzwalacz zakończy się błędem i transakcja zostanie wycofana, skutki powiązanych modyfikacji również nie powinny pozostać zapisane.
W wyzwalaczach INSTEAD OF tabela deleted opisuje wiersze objęte żądaniem usunięcia albo aktualizacji, ale sama operacja na tabeli bazowej nie jest automatycznie wykonywana w zwykły sposób. Kod wyzwalacza musi świadomie określić, jakie działania mają nastąpić. Ma to znaczenie zwłaszcza przy widokach, ponieważ wyzwalacz może przejąć obsługę modyfikacji widoku i rozdzielić dane do jednej lub kilku tabel bazowych. Tabela deleted nadal oznacza wtedy wartości przed żądaną zmianą, a nie dodatkowy magazyn kopii zapasowych.
Znaczenie deleted przy analizie działania bazy danych
Podczas diagnostyki problemu z wyzwalaczem należy najpierw ustalić, jaka instrukcja go uruchamia, czy jest to wyzwalacz AFTER czy INSTEAD OF, oraz czy operacja obejmuje jeden, czy wiele wierszy. Następnie warto sprawdzić, czy zapytania korzystające z deleted uwzględniają klucze główne, wartości NULL i możliwość aktualizacji kilku rekordów jednocześnie. Błędne założenie o pojedynczym wierszu jest jedną z typowych przyczyn niepełnego zapisu historii lub nieprawidłowego odświeżania danych zależnych.
Nie należy traktować deleted jako trwałej historii zmian. Zawartość tej tabeli logicznej istnieje tylko w trakcie wykonywania wyzwalacza i odnosi się wyłącznie do bieżącej instrukcji. Jeżeli wymagany jest długoterminowy rejestr zmian, musi zostać jawnie zapisany w przeznaczonej do tego tabeli albo obsłużony mechanizmem audytowym właściwym dla SQL Server. Sama obecność tabeli deleted nie gwarantuje zachowania danych po zakończeniu transakcji i nie zastępuje kopii zapasowej.
W praktyce kontrola wyzwalacza powinna obejmować testy dla usunięcia jednego rekordu, usunięcia wielu rekordów, aktualizacji bez faktycznej zmiany wartości oraz aktualizacji zmieniającej kolumny zawierające NULL. Należy również zweryfikować zachowanie po wycofaniu transakcji i przy naruszeniu ograniczeń integralności. Takie przypadki pokazują, czy kod poprawnie interpretuje wiersze z deleted, czy nie gubi części zbioru oraz czy operacje zależne pozostają spójne z wynikiem działania instrukcji DML.
