Wyzwalacz odrzuca aktualizację wiersza, którego nie ma w tabeli

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Programista albo administrator baz, który wdraża import do tabeli słownikowej, chce, żeby brakujący klucz zatrzymywał zapis, a nie cicho pomijał wiersz. Pokazujemy wzorzec: wyzwalacz AFTER INSERT na tabeli importu sprawdza, czy każdy klucz ma wpis w słowniku, zanim cokolwiek zmieni. Brak wpisu zgłasza THROW z własnym numerem i zostawia słownik bez zmian, a wartości sprzed zmiany trafiają do dziennika. Nazwy tabel i kolumn w kodzie są przykładowe.

Wyzwalacz w T-SQL odrzuca aktualizację wiersza, którego nie ma w tabeli. Sprawdzenie idzie przed UPDATE, a brak wpisu kończy się błędem 50004 i komunikatem o odrzuconym wierszu. Wartości sprzed zmiany zapisują się w dzienniku tylko dla wpisów zmienionych. Wynik testu jest taki sam na poziomach zgodności 130 i 160.

Słownik nie przyjmie wiersza bez wpisu, bo import tylko aktualizuje

Import w tym wzorcu ma dwa różne zadania. Jeden zapis dopisuje nowe wpisy do słownika, a drugi tylko aktualizuje wpisy, które już są. Wyzwalacz działa po wstawieniu do tabeli importu i obsługuje wyłącznie aktualizację istniejących wpisów. Nowe wpisy dodaje osobny przycisk importu z innym schematem.

• Import aktualizujący: zmienia tylko istniejące wpisy.
• Import dopisujący: osobny przycisk, który tworzy nowe wpisy.

Przy imporcie, który ma tylko aktualizować, wiersz bez wpisu oznacza błąd w pliku, a nie brakującą pozycję do dopisania. Zatrzymanie całej porcji wymusza więc poprawienie pliku albo dopisanie wpisu osobnym krokiem. Zespół importujący dostaje wtedy jeden komunikat zamiast częściowego stanu słownika.

Z tego samego powodu wzorzec nie dopuszcza częściowego zapisu porcji, bo wycofanie obejmuje całą instrukcję. Błąd danych w jednym wierszu nie może przejść niezauważony, bo zapis zostaje zatrzymany przed jakąkolwiek zmianą słownika. Zakres importu tłumaczeń, którego dotyczy ten wzorzec, opisuje artykuł o imporcie tłumaczeń.

Wyzwalacz sprawdza klucz każdego wiersza przed pierwszą zmianą

Klucz dopasowania to kod języka i indeks. Wzorzec używa dokładnie tych dwóch kolumn, bo tak zbudowany jest indeks tabeli docelowej, który nie dopuszcza dwóch wpisów o tym samym kluczu.

Sprawdzenie brakujących wpisów idzie przed UPDATE tabeli docelowej. Najpierw wykonuje się sprawdzenie z THROW, a dopiero potem aktualizacja. Warunek sprawdza istnienie wpisu dla każdej wartości klucza z tabeli wstawionej, co obejmuje wszystkie wiersze porcji. Komunikat, który pojawia się przy odrzuceniu, brzmi tak, jak w cytacie poniżej.

Aktualizacja słownika: brak wpisu dla kodu i indeksu, wiersz odrzucony.

Ta kolejność ma znaczenie, bo THROW przerywa instrukcję, zanim jakakolwiek wartość w słowniku się zmieni. Wzorzec nie zapisuje więc żadnej części porcji przed sprawdzeniem wszystkich kluczy. Schemat poniżej pokazuje przepływ od tabeli importu do słownika.

Wartość sprzed zmiany trafia do dziennika tylko dla zmienionych wpisów

Dziennik zapisuje wartość sprzed zmiany tylko dla wpisów, których tekst się zmienił. Wartość sprzed zmiany zapisuje się dla każdego wpisu, który został zaktualizowany, a nie dla wpisów, które pozostały bez zmian. Dziennik pokazuje więc dokładnie to, co zmieniło się w słowniku, i zawiera historię zmian jednego klucza, a nie wszystkich wierszy porcji.

• Dziennik: wartość sprzed zmiany, tylko dla faktycznie zmienionych wpisów.
• Duplikat klucza: w porcji wygrywa wiersz z najwyższym Id.

Wartości sprzed zmiany pozwalają cofnąć import jednym UPDATE. Dla zespołu oznacza to, że przywrócenie wartości wymaga tylko wpisów z dziennika. Gdy ten sam klucz pojawia się kilka razy w porcji, wygrywa wiersz z najwyższym Id, co wybiera zapytanie z MAX.

Przykład do uruchomienia składa się z trzech bloków

Pierwszy blok tworzy trzy tabele. Słownik ma klucz złożony z kodu i indeksu, tabela importu ma kolumnę Id, a dziennik ma kolumny wartości przed zmianą i po niej. Drugi blok zawiera wyzwalacz, a trzeci test. GO jest separatorem narzędzia, a nie częścią języka SQL.

CREATE TABLE dbo.SlownikOpisow (
    Kod    varchar(10)   NOT NULL,
    Indeks varchar(30)   NOT NULL,
    Tekst  nvarchar(200) NOT NULL,
    CONSTRAINT PK_SlownikOpisow PRIMARY KEY (Kod, Indeks)
);
CREATE TABLE dbo.SlownikOpisowImport (
    Id     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SlownikOpisowImport PRIMARY KEY,
    Kod    varchar(10)   NOT NULL,
    Indeks varchar(30)   NOT NULL,
    Tekst  nvarchar(200) NOT NULL
);
CREATE TABLE dbo.SlownikOpisowDziennik (
    Id         int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SlownikOpisowDziennik PRIMARY KEY,
    Kod        varchar(10)   NOT NULL,
    Indeks     varchar(30)   NOT NULL,
    TekstPrzed nvarchar(200) NOT NULL,
    TekstPo    nvarchar(200) NOT NULL,
    Kiedy      datetime      NOT NULL CONSTRAINT DF_SlownikOpisowDziennik_Kiedy DEFAULT GETDATE()
);
GO

Przykład ilustracyjny, nazwy wymyślone. Trzy tabele pomocnicze z przykładu.

Wyzwalacz uruchamia się po wstawieniu wiersza do tabeli importu. Najpierw sprawdza warunek z THROW. Potem wpisuje do dziennika wartość sprzed zmiany i nową, ale tylko dla wiersza o najwyższym Id w obrębie klucza i tylko wtedy, gdy tekst się różni. Na końcu aktualizuje słownik tym samym wierszem.

CREATE TRIGGER dbo.tr_SlownikOpisowImport_AI
ON dbo.SlownikOpisowImport
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Import tylko aktualizuje. Wiersz bez wpisu w słowniku odrzuca całą porcję.
    IF EXISTS (SELECT 1 FROM inserted i
               WHERE NOT EXISTS (SELECT 1 FROM dbo.SlownikOpisow s
                                 WHERE s.Kod = i.Kod AND s.Indeks = i.Indeks))
        THROW 50004, N'Aktualizacja słownika: brak wpisu dla kodu i indeksu, wiersz odrzucony.', 1;

    -- Ten sam klucz kilka razy w porcji: wygrywa wiersz wstawiony najpóźniej.
    -- Dziennik: wartość sprzed zmiany, tylko dla faktycznie zmienionych wpisów.
    INSERT INTO dbo.SlownikOpisowDziennik (Kod, Indeks, TekstPrzed, TekstPo)
    SELECT s.Kod, s.Indeks, s.Tekst, i.Tekst
    FROM inserted i
    JOIN dbo.SlownikOpisow s ON s.Kod = i.Kod AND s.Indeks = i.Indeks
    WHERE i.Id = (SELECT MAX(j.Id) FROM inserted j WHERE j.Kod = i.Kod AND j.Indeks = i.Indeks)
      AND s.Tekst <> i.Tekst;

    UPDATE s
    SET s.Tekst = i.Tekst
    FROM dbo.SlownikOpisow s
    JOIN inserted i ON s.Kod = i.Kod AND s.Indeks = i.Indeks
    WHERE i.Id = (SELECT MAX(j.Id) FROM inserted j WHERE j.Kod = i.Kod AND j.Indeks = i.Indeks);
END;
GO

Przykład ilustracyjny, nazwy wymyślone. Wyzwalacz AFTER INSERT na tabeli importu.

Test ma trzy kroki, a przy każdym jest komentarz. Pierwszy krok dopisuje wpis do dziennika, drugi pokazuje odrzucenie porcji, a trzeci sprawdza, który wiersz wygrywa przy powtórzonym kluczu.

1. Wiersz z wpisem: aktualizacja i zapis wartości sprzed zmiany.
2. Porcja z wierszem bez wpisu: cała porcja odrzucona, słownik bez zmian.
3. Ten sam klucz dwa razy w porcji: wygrywa wiersz z wyższym Id.

INSERT INTO dbo.SlownikOpisow (Kod, Indeks, Tekst)
VALUES ('PL', 'A-100', N'Stara treść'), ('DE', 'A-100', N'Alte Beschreibung');
-- 1. Wiersz z wpisem: aktualizacja i zapis wartości sprzed zmiany.
INSERT INTO dbo.SlownikOpisowImport (Kod, Indeks, Tekst) VALUES ('PL', 'A-100', N'Nowa treść');
SELECT Kod, Indeks, Tekst FROM dbo.SlownikOpisow WHERE Kod = 'PL';
SELECT Kod, Indeks, TekstPrzed, TekstPo FROM dbo.SlownikOpisowDziennik;
-- 2. Porcja z wierszem bez wpisu: cała porcja odrzucona, słownik bez zmian.
BEGIN TRY
    INSERT INTO dbo.SlownikOpisowImport (Kod, Indeks, Tekst)
    VALUES ('DE', 'A-100', N'Zmiana z porcji'), ('FR', 'A-100', N'Brak wpisu');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Blad, ERROR_MESSAGE() AS Komunikat;
END CATCH;
SELECT Kod, Tekst FROM dbo.SlownikOpisow WHERE Kod = 'DE';
SELECT COUNT(*) AS WierszyDziennika FROM dbo.SlownikOpisowDziennik;
-- 3. Ten sam klucz dwa razy w porcji: wygrywa wiersz z wyższym Id.
INSERT INTO dbo.SlownikOpisowImport (Kod, Indeks, Tekst)
VALUES ('PL', 'A-100', N'Druga'), ('PL', 'A-100', N'Trzecia');
SELECT Kod, Indeks, Tekst FROM dbo.SlownikOpisow WHERE Kod = 'PL';
SELECT COUNT(*) AS WierszyDziennika FROM dbo.SlownikOpisowDziennik;

Przykład ilustracyjny, dane wymyślone. Test z trzema krokami; komentarze w kodzie opisują kroki.

Wynik jest taki sam na poziomach zgodności 130 i 160

Tabela pokazuje wynik trzech kroków testu. Wynik na poziomie 130 i na poziomie 160 jest identyczny, a numer błędu 50004 zgłasza wyzwalacz, a nie silnik bazy, więc nie zmienia się między poziomami. Wynik testu opisuje zachowanie wzorca, a nie wdrożenia u klienta.

Wynik jest zgodny w każdym kroku na poziomie zgodności 130 i na poziomie 160.

Trzeci krok pokazuje regułę najwyższego Id. W słowniku zostaje „Trzecia”, a dziennik dostaje drugi wpis, z wartością „Nowa treść” przed zmianą i „Trzecia” po niej. Makieta poniżej przedstawia stan słownika i dziennika po drugim kroku, z odrzuconym wierszem.

Sposób sprawdzenia polega na uruchomieniu bloków z przykładu na dwóch poziomach zgodności w jednorazowej bazie i porównaniu wyników z tabelą. Po teście bazę usuwa się, bo służy tylko do sprawdzenia wzorca.

Wzorzec zatrzymuje całą porcję i nie zastępuje kontroli w aplikacji

Wzorzec zatrzymuje cały zapis, gdy choć jeden wiersz porcji nie ma wpisu. Nie nadaje się więc do sytuacji, w których część wierszy ma przejść, a część nie, bo instrukcja zostaje wycofana w całości. Odrzucony wiersz nie trafia do dziennika, bo wycofanie obejmuje też wpisy dziennika z tej samej instrukcji.

• Brak wpisu w porcji: zatrzymuje cały zapis.
• Odrzucony wiersz: nie zostaje zapisany w dzienniku w tym wzorcu.

Przykład (dane wymyślone): zespół przygotowuje plik ze zmienionymi opisami dla dwóch artykułów. Jeden z nich nie ma jeszcze wpisu w słowniku. Cały plik nie trafia do słownika, a zespół dostaje komunikat z numerem błędu 50004. Brakujący wpis dopisuje się osobnym krokiem, a plik wgrywa się ponownie. Dziennik pokazuje, jaka treść była w słowniku przed każdą zmianą.

Brakujące wpisy można sprawdzić zapytaniem jeszcze przed wgraniem pliku do tabeli importu. Wyzwalacz nie sprawdza uprawnień konta, bo tego zadania nie ma w tym wzorcu, i nie zastępuje projektu kontroli danych wejściowych w aplikacji. Wzorzec zakłada klucz w tabeli słownika. Bez indeksu, który nie dopuszcza dwóch wpisów o tym samym kluczu, test brakującego wpisu może trafić w kilka wierszy naraz.

Pytania do analizy

Przed wdrożeniem podobnego importu odpowiedz na cztery pytania.

1. Które pola klucza muszą być w pliku importu, a które można uzupełnić z istniejącego wpisu?
2. Czy odrzucenie całej porcji jest lepsze niż zapis części wierszy w tej samej operacji?
3. Jak długo trzeba przechowywać wartości sprzed zmiany i kto ma do nich dostęp?
4. Co się stanie, gdy dwie osoby zaimportują ten sam klucz tego samego dnia?