Mssql instead of trigger – implementacja

Powrót

Praca wre, coś się kompiluje, coś się linkuje coś się uruchamia, a coś nie. Tym czymś na „nie” była (była bo już rozwiązane) baza mssql. Problem który napotkałem wyglądał tak:

Geneza:

Chce aby oprócz śledzenia kanałów z RSSami, użytkownik śledzić mógł też poczynania innych użytkowników. Aby tego dokonać potrzebuje gdzieś w bazie danych posiadać klasę/tabelę, która będzie trzymać informację o tym kto i kogo obserwuje.
Uproszczona klasa użytkownika prezentuje się tak:

Następnie mamy klasę, która zawiera subskrypcję, która może wyglądać w taki sposób:

Jak to rozumieć? Subskrypcja posiada informację tym kto obserwuje (@6) i informację o tym kogo obserwuje (@9).

Po wygenerowaniu migracji w entity framework, tworzy się domyślnie taki zapis:

Ważne są tutaj dwie linijki (ważne, bo o to nich ten wpis na blogu) @11 i @12. Ustawione zostają tam klucze obce oraz reguły aktualizacji, na wypadek zmiany w klasie do której referują. W moim przypadku dwa razy wskazuje na Usera i mówię, że gdy user się usunie, to usuń i subskrypcję. Zarówno dla osoby obserwującej jak i obserwowanej. Oczywista oczywistość – nie będzie komu lub kogo obserwować. Uruchamiam: Update-Database <enter> …

Ból

I teraz na scenę wchodzi mssql: (cały na biało)
Introducing FOREIGN KEY constraint ‚XXX’ on table ‚XXX’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

W pierwszych odruchu zacząłem szukać zależności cyklicznych, bo może sam sobie ojcem, synem, wujkiem i dziadkiem. Nie. Otóż chodzi o specyficzny przypadek, którego biznes oczywiście nie dopuszcza, a baza fizycznie tak. Otóż można dodać wpis, gdzie obserwowany i obserwujący to ta sama osoba (to samo id), w takim przypadku gdy zostanie usunięty User, należy usunąć subskrypcje, ale dla mssql to problem, bo musiałby to zrobic dwa razy – a nie może, bo juz raz zostało to usunięte. klops. Internety mówią, że inne bazy danych robią wyścig, ale pozwalają na taki zapis – no cóż, nikt mi nie kazał używac mssql.

Pomocy

Jak z tym żyć? Google oraz SO mówią: triggery. Ja mówię: matko, jak ja nienawidzę baz danych.
Triggery to obsługa wydarzeń dla bazy danych, przy ich pomocy można wykonać procedurę po takich akcjach: insert, update, delete.

Czyli zmieniam podejście u siebie w kodzie i decyduję się na brak jakiejkolwiek kaskadowości, flagi: cascadeDelete: true zmienią się na false.
Ale czy na pewno? Skoro trigger zostanie uruchomiony po usunięciu, to na co będą wskazywać klucze obce skoro user został wywalony? Znowu pierwsza myśl (leniwa) to NULL – ale zaraz odezwał się maruda: „ja ja nienawidzę nuli”. Nie! Musi być lepszy sposób. Na szczęście, od którejś tam wersji mssql można stosować triggery zamiast samej operacji – instead of. Czyli mssql dopuszcza nadpisanie operacji delete samodzielnie zdefiniowanym triggerem. Zaswędziały rączki, google oraz SO zapłonęły pod nawałem pytań, które generowałem „howto”, „tutorial”, „śmieszne kotki”. Ostatecznie zdecydowałem się na krótke rozwiązanie, które spełnia moje wymagania:

Tworzę triggera, używając składni INSTEAD OF XXX (@3) mówię co chce przeciążyć. Następnie samodzielnie usuwam zależne od siebie encje (@6 i @7). Dostęp do usuniętych/usuwanych jest w tabeli deleted (odpowiednio modified, inserted – dla osobnych wydarzeń). Ostatecznie usuwam także użytkowników.
Oczywiście są inne tabele, zależne od usera, ale o nich nie muszę pamiętać, one i ich klucze obce nadal pracują z ustawioną kaskadą, która zaskoczy przy właściwym usunięciu użytkownika.

I tyle sprawa rozwiązana. Cały kod dostępny pod adresem:
https://github.com/jstadnicki/isthereanynews

14 thoughts on “Mssql instead of trigger – implementacja

  1. Jako, że trigger obsługuje wywalenie usera to nie lepiej samemu zadbać o wyczyszczenie jego połączeń z subskrypcjami w metodzie usuwającej użytkownika? Wg mnie jest to czytelniejsze, bo wszystko w jednym miejscu się dzieje.

    Triggery zawsze dostarczały mi problemów, jak się o nich zapomniało to czasem było ciężko dojść co, gdzie i czemu się stało (teraz już mam doświadczenie i jak coś nie jest jasne od razu sprawdzam czy nie ma triggera na tabelach ;)).

    Nie słyszałem o „instead of”, jak już trzeba użyć triggera to fajnie, że istnieje taka opcja :).

    1. Myślałem o tym, żeby robić to ręcznie. Ale wtedy część byłaby ręczna, (cascade: none) a część automatycznie (cascade:delete), to moim zdaniem powoduje, że nie wiesz czy brak ręcznego usunięcia to błąd, czy może zamierzone działanie.

  2. W takim wypadku byłbym za tym, że wszystko jest jawne i nie korzystam z innej opcji niż cascade: none. Więcej pisania, więcej myślenia (chociaż tutaj nas zawsze exception dziabnie w tyłek jak nie pomyślimy o wszystkich obcych kluczach), ale dzięki temu rzucam okiem na metodę i widzę: aha przed usunięciem usera wywalam jego subskrypcję.

    Triggery i kaskady powodują, że jak zapomnę o nich (lub nie będę o nich wiedział, bo dołączyłem dopiero co do projektu) to usuwając usera nie jestem świadom, że razem z nim leci x danych.

  3. Pingback: dotnetomaniak.pl
  4. Tak jak moi przedmowcy, wylaczyl bym kaskade i w tranzakcji usunal rekord i jego zaleznosci, nie ma sensu mieszac akcji w kodzie i DB.

    1. Jasne, mieszanie jest złe. Dlatego tego nie robię. Wszystko jest na kaskadach i taką mam politykę w całym projekcie.
      Być może kiedyś to się zmieni, ale teraz póki co to się sprawdza, a jeśli się sprawdza to zostawiam. Może być tak, że kiedyś wrócę do tego posta i napiszę – „a mieli rację”.

      1. Oby z czasem pamiec o tych triggerach nie zniknela, bo wtedy moze byc wesolo, juz przechojmowalem projekt gdzie dziala sie wielka magia, a jej autor zapomnial wspomniec w dokumentacji, ze poza EF i migracjami istnieje recznie dodana warstwa logiki DB w triggerach.

        1. Jasne, rozumiem. Póki co, wydaje mi się, że kaskady jest logicznym podejściem. Poza tym, gdzie popełniać błędy i uczyć się na nich, jeśli nie na projektach domowych?

      1. Wydaje mi się, że kasowanie Usera to dość drastyczna operacja – tracimy informację o tym, że był, historię jego działań, transakcji, operacji (w zależności od tego, czego system dotyczy). Do tego, właśnie przez nadmierne `on delete cascade` można sobie przypadkiem pół bazy skasować. Generalnie nie jestem zwolennikiem fizycznego kasowania ważnych encji z bazy, myślę że do takich rzeczy bardziej pasuje soft delete.

        1. 1. Mam tempo odpowiedzi.
          2. Nie odbierz mnie źle, ale dlaczego wchodzisz z butami w plan biznes?
          3. Idealnie tak, fajnie byłoby nie kasować użytkownika z systemu, tylko zostawiać go nie aktywnym:
          – jeszcze tam nie jestem
          – nie wiem czy tak będę chciał
          – nie wiem, jak ostatecznie wszystkie GIODO podchodzą do sprawy usunięcie konta z systemu
          – taki był po prostu przypadek i akurat napatoczył mi się przy usuwaniu konta użytkownika.
          – trzymanie miękko usuniętych powoduje dodatkowe „WHERE” w zapytaniach – niby nic, a jednak
          – nie szukam wymówek, to po prostu kilka odpowiedzi tak z głowy, dlaczego aktualnie usuwam użytkownika z bazy

          1. Przepraszam jeśli tak to odebrałeś, nigdzie nie chciałem Ci wchodzić z butami. Jedynie rzuciłem taką ogólnie-filozoficzną uwagę, nie miałem na myśli, że coś robisz źle.

            Z moich doświadczeń wynika, że z bazy raczej usuwa się obiekty tymczasowe albo błędnie wprowadzone. Jeśli jakikolwiek rekord miał jakiś sens biznesowy przez jakiś czas, to raczej usuwa się go jedynie w sposób soft. Szczerze mówiąc, nie przypominam sobie, abym kiedykolwiek widział to inaczej zrealizowane. Tylko tyle.

          2. No co ty – jest git. Pytanie oczywiście jak najbardziej na miejscu, moja odpowiedź lekko-prowokacyjna, aby dowiedzieć się trochę czemu tak sądzisz. A z drugiej strony kilka odpowiedzi na to, że nie zawsze jest tak, jak jesteśmy do czego przyzwyczajeni, czyli „soft delete” przyklejony do każdej operacji.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *