Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

SQL - Trigger porównujący dane z tabeli Inserted

aksimoN 16 Lip 2012 15:25 3659 20
  • #1 16 Lip 2012 15:25
    aksimoN
    Poziom 10  

    Próbuję napisać trigger'a sprawdzającego insertowane dane pod kątem występowania ich w docelowej tabeli:

    Kod: sql
    Zaloguj się, aby zobaczyć kod


    chodzi o operowanie tabelą INSERTED zawierającą insertowane dane. Jeśli w tej tabeli znajduje się identyczny wpis jak w tabeli docelowej następuje wyrzucenie błędu i rollback transakcji. Mój problem polega na tym iż napisany przeze mnie trigger nie pozwala na insert żadnych danych. Jeśli zamiast tabeli INSERTED użyje innej zwykłej tabeli działa to prawidłowo. Może ktoś wie gdzie robię błąd i jak się obsługuje tabelę INSERTED dostępną w triggerach

    0 20
  • #2 16 Lip 2012 16:44
    Dżyszla
    Poziom 42  

    Po pierwsze, jeśli chcesz chronić przed dodaniem danych, to musisz wykonać BEFORE INSERT a nie AFTER. Po drugie takie zapytanie byłoby niepotrzebnie wolne - po co JOIN? Po trzecie - najważniejsze - po co? Od takich rzeczy są indeksy unikalne.

    0
  • #3 16 Lip 2012 18:03
    aksimoN
    Poziom 10  

    ad1. W MS SQL'u nie ma BEFORE INSERT
    ad2. Czemu miałoby być wolne? a nawet jeśli to wyjdzie w praniu czy rzeczywiście okaże się za wolne
    ad3. Nie ogarniam jak mam to zrobic na unikalnych indeksach ponieważ tabela ta nie składa się z takowych, unikalny na tabeli ma być tylko cały zestaw pól czyli np.

    pole_1 pole_2 pole_3 pole_4
    1 1 1 1
    1 1 1 2
    1 2 1 1
    1 2 1 2

    Tylko jeśli cały insert'owany do tabeli wiersz jest identyczny z wierszem który znajduje się już w tabeli ma zadziałać trigger czyli np nie pozwoli wpisać wierszy
    1 1 1 1
    czy też
    1 2 1 2
    a pozwoli na każdą inną kombinacje tych czterech pól której jeszcze nie ma w tabeli

    ps. Działał już ktoś na tabeli INSERTED?!?

    0
  • #4 16 Lip 2012 18:08
    Dżyszla
    Poziom 42  

    Robisz indeks na kilka pól i czynisz go unikalnym. Ot co.

    PS. Jeśli trigger wywołuje się AFTER INSER to jedyne co robi, to sprawdza, czy rekord się rzeczywiście dodał (w obrębie transakcji) co skutkuje tym, że nie możesz dodać niczego.

    0
  • #5 17 Lip 2012 11:09
    aksimoN
    Poziom 10  

    Wszystko fajnie unique index na wszystkie pola rozwiązał problem unikalnych zestawów danych jednakże podczas insert'owania potrzebuję jeszcze sprawdzić i zmienić wartość jednego pola w wierszu znajdującym się w tabeli w którym wartości z 2 innych pól są takie same jak w wierszu właśnie zainsertowanym coś na zasadzie:

    pole_1 pole_2 pole_3 pole_4
    1 1 1 1
    1 2 1 1
    insertując:
    2 1 1 1
    pole_2 i pole_3 są takie same jak w pierwszym wierszu znajdującym się w tabeli to pole_4 wiersza w tabeli zmieniam na 0 czyli tabela po insercie ma wyglądać tak:
    pole_1 pole_2 pole_3 pole_4
    1 1 1 0
    1 2 1 1
    2 1 1 1

    Czyli nadal chcę porównywać właśnie zainsertowany wiersz z wierszami z tabeli. Z tego co szukałem to chyba najłatwiej było by korzystać z tabeli inserted

    0
  • #6 17 Lip 2012 11:29
    arnoldziq
    Moderator Programowanie

    Panowie.
    A nie lepiej dać po prostu "Insert OR Update" i trigger dać na dowolną funkcję update?
    Wilk syty i owca cała. -> rekordy nie będą się powtarzać, a trigger wyświetli komunikat.

    0
  • #7 17 Lip 2012 11:40
    aksimoN
    Poziom 10  

    To o co pierwotnie chodziło zostało zrobione unique index'em, ale nadal chodzi o możliwość operowania tablicą inserted aby wyszukiwać w tabeli wiersze podobne do tych co są w tabeli inserted i odpowiednio je modyfikować mniej więcej tak jak napisałem w poprzednim poście. Przykład jest dużo prostszy niż to jak to w rzeczywistości będzie wyglądało jednakże sens jest mniej więcej taki. Dlatego nadal się rozchodzi o możliwość operowania na tablicy inserted

    0
  • #8 17 Lip 2012 12:00
    roofy_1
    Poziom 16  

    Tabela "inserted" w tranzakcyjnych bazach jak sama nazwa wskazuje zawiera zapisane w pamięci dane ostatnio, prawidłowo wprowadzone do bazy.
    Z tego co zrozumiałem to chcesz porównywać obecnie wprowadzany rekord ze wszystkimi obecnymi w bazie a tego table inserted Ci nie da
    Najpierw po kolei krok po kroku zastanów się co chcesz osiągnąć. Napisz sobie ładnie, na początek selecta, który pozwoli Ci na wyszukiwanie rekordów spełniających Twoje kryteria przed wstawieniem krotki do bazy. Potem spróbuj zaszyć to w procedurze i dopiero wtedy na wstawienie rekordu uruchamiaj trigger.
    Zwróć uwagę na to, że:

    Cytat:

    pole_1 pole_2 pole_3 pole_4
    1 1 1 1
    1 2 1 1
    insertując:
    2 1 1 1


    Musisz to najpierw znaleźć w bazie. Dalej:
    Cytat:
    pole_1 pole_2 pole_3 pole_4
    1 1 1 0
    1 2 1 1
    2 1 1 1


    Robisz update rekordu spełniającego kryteria.
    Czyli generalnie nie jest tak hop siup :).

    Poza tym co jeśli będziesz chciał wstawić jeszcze raz rekord podobny do 2,1,1,1? I co nadal zapytanie spełnia kryteria 1,1,1,0 c z zerem?

    0
  • #9 17 Lip 2012 12:13
    aksimoN
    Poziom 10  

    Drugi raz rekord 2,1,1,1 nie zostanie wstawiony ponieważ unique index na to nie pozwoli, jeśli chodzi o rekord podobny np. 3,1,1,1 będzie on musiał zmienić tylko te rekordy w których na ostatnim polu jest 1 (pole_4 to taki jakby status) czyli 1,1,1,0 zostaje nie zmienione ponieważ ma status 0 (nie bierzemy pod uwagę) za to 2,1,1,1 zostanie zmienione na 2,1,1,0 a 3,1,1,1 zostanie dopisane do tabeli... Sama postać select'ów tez nie będzie prosta ponieważ pola 2 i 3 są to klucze obce z innej tabeli i dopiero porównanie danych z tamtych tabel ma skutkować decyzją o zmianie statusu ale to chce sobie zostawić na później... Najpierw chciałem dotrzeć do tego jak porównywać to co zostało właśnie wstawione do tabeli z tym co już na niej jest

    0
  • #10 17 Lip 2012 12:27
    roofy_1
    Poziom 16  

    Dokładnie :)
    Widzisz sam sobie odpowiedziałeś :)
    Zdaję mi się, że trochę od "duszy" strony zacząłeś napisz najpierw tego selecta z przykładowymi danymi i sprawdź czy spełnia kryteria. Potem procedura z parametrami gdzie do update wykorzystujesz to Twoje zapytanko z selecta i w tej samej procedurze uruchamiasz inserta jeśli rekord spełnia warunki. Potem to wszystko uruchamiasz w triggerze.

    0
  • #11 17 Lip 2012 12:32
    aksimoN
    Poziom 10  

    Tak to jest jak się wie że ma to być zrobione trigger'em a nigdy się go nie pisało. Spoko napiszę sobie na razie całą "procedurę" na konkretnym przykładzie, potem ją sparametryzuję ale i tak na koniec będę musiał dojść do tego jak pod parametry podstawić dane z insert'owanego wiersza

    0
  • #13 17 Lip 2012 17:47
    aksimoN
    Poziom 10  

    No dobra mam już wszystko w procedurce której zmiennymi wejściowymi są pola insert'owanego wiersza... Procedurka ma być wywoływana w trigger'ze AFTER INSERT... Jak przekazać jej zmienne wejściowe?!?

    Proszę poprawić pisownię (dot. również wcześniejszych postów w tym wątku). [adamas_nt]

    Chodzi o duże litery?!?

    0
  • #14 18 Lip 2012 08:27
    roofy_1
    Poziom 16  

    Spróbuj teraz z czymś takim:

    Kod: sql
    Zaloguj się, aby zobaczyć kod


    Na początku tworzysz - "Create" trigger a potem zmieniasz na "Alter". Deklarujesz tyle zmiennych ile potrzebujesz.
    Zrobiłeś też w tej procedurze update rekordu istniejącego w bazie?

    0
  • #15 18 Lip 2012 16:13
    aksimoN
    Poziom 10  

    Tak tak mam zrobione update'y rekordów znajdujących się już w bazie. Mam też już trigger'a ale na razie zrobionego w taki sposób:

    SET @zmienna1 = (SELECT TOP 1 pole_1 FROM INSERTED)
    SET @zmienna2 = (SELECT TOP 1 pole_2 FROM INSERTED)

    i tak dalej...

    Ponieważ działa to teraz tylko dla pierwszego wpisu w tabeli INSERTED, chciałbym to zmienić jakoś na kształt pętli typu: weź pierwszy wpis z INSERTED i wykonaj dla niego procedurę, weź drugi wpis i wykonaj procedurę i tak dalej aż do końca wszystkich wierszy w INSERTED i tu mam mały problem:

    SELECT row_number() over (order by aplID) as KTORY, * from INSERTED WHERE ktory = 5

    Bez WHERE to normalnie zadziała i ponumeruje mi wiersze ale jak już chcę wyświetlić tylko jeden z nich (w tym przypadku 5) to krzyczy że nie ma kolumny KTORY. Nie wiem w takim razie jak pętlą przejść przez całą tablicę inserted

    0
  • #16 18 Lip 2012 18:35
    roofy_1
    Poziom 16  

    to zrób tak:

    Kod: sql
    Zaloguj się, aby zobaczyć kod

    0
  • #17 19 Lip 2012 12:04
    aksimoN
    Poziom 10  

    Śmiga ale, zawsze jest jakieś ale, procedura wykonuje insert a potem update i jeśli któraś z tych operacji wywoła błąd (próba wpisu lub zmiany wpisu na dubla, pilnowane przez index) to po pierwsze przestaje się wykonywać a po drugie nie wykonuje dalszych insertów (procedura uruchamiana za pomocą trigger'a AFTER INSERT, UPDATE) idzie to łatwo obejść? Na zasadzie spoko jest błąd ale pracuj dalej..? Z mojego punktu widzenia nie ma sensu obsługa błędu za pomocą TRY CATCH ale może się mylę.

    0
  • #19 20 Lip 2012 14:23
    aksimoN
    Poziom 10  

    Przemyślałem sprawę i chciałem zrobić coś na zasadzie IF NOT EXISTS(select) INSERT tylko że ten insert nie jest na tyle prosty aby przebudować go na select'a aby sprawdzić czy już coś takiego istnieje (szkoda że nie można od razu IF NOT EXISTS (INSERT)) no ale do rzeczy. Błąd który wywala i zatrzymuje mi działanie procedury nie jest dla mnie istotny. Problem leży w tym iż to trigger dla każdego insert'owanego wpisu wywołuje w pętli tą procedurę więc może wystarczy ustawić coś w trigger'owej pętli? Po wywołaniu przez procedurę błędu nie przerywaj tylko skocz do następnego inserta? Chyba że idzie oprogramować index tak aby w przypadku wspomnianego "dubla" nie przerywało wykonania procedury/trigger'a

    0
  • #20 20 Lip 2012 14:48
    roofy_1
    Poziom 16  

    Wiesz sam już nie jestem pewien czy to w dobrą stronę idzie. Trigger wyzwalany jest w skutek jakieś operacji insertu update lub delete. I to odbywa się jednorazowo w momencie jakieś akcji a Ty chyba chcesz coś zrobić w sposób bardziej "masowy". Pokaż to co do tej pory napisałeś bo coś mi tu nie gra :) albo wrzuć na PW

    0
  • #21 21 Sie 2012 08:11
    aksimoN
    Poziom 10  

    Trigger wywołuje i przekazuje dane nowego wpisu procedurze która rozwiązuje wcześniej założony problem

    0