Trigger sprawdzający istnienie rekordu

0

Hej!

Nie mogę rozgryźć dlaczego po dodaniu tego triggera, który ma tylko sprawdzić czy dany wpis do tabeli VOTES (zawierający 2 wartości) już istnieje, nie mogę dodać rekordu, który nie istnieje.

create or replace trigger czy_juz_istnieje
  before insert on VOTES
  for each row
    declare
    id_uzytkownika number(10,0) := :new.vote_userID;
    id_odpowiedzi number(10,0) := :new.vote_answerID; 
    istnieje varchar2(1 char) := 0;
    
    begin
    -- jesli istnieje, zmienna istnieje wynosi 1:
      select 1 into istnieje from VOTES where VOTE_ANSWERID = id_odpowiedzi and VOTE_USERID = id_uzytkownika;
    if istnieje > 0
    then
     RAISE_APPLICATION_ERROR(-20666, 'JUŻ GŁOSOWAŁEŚ!');
    else
     if inserting
       then insert into VOTES values (id_uzytkownika,id_odpowiedzi);
     end if;
    end if;

end;

set transaction name 'aaa';
insert into VOTES values (1,22);
commit;

Jeśli wpis istnieje, zwraca mi pożądany błąd, ale gdy nie istnieje, do bazy nie dodawany jest żaden wpis :|

0

A id wpisu? To co tutaj widzę to wstawienie dwóch id z innych tabel, a samo id nie występuje. A wywala się jakiś błąd? Powinien krzyknąć że nie można wstawić wartości bez PK

0

Po co Ci podwójny insert (pewnie bazie to się nie podoba, bo ten insert z trigera wywoła ponownie trigger, itp)? Wystarczy, że w triggerze zablokujesz insert jezeli ma go nie być, a jeżeli chcesz, żeby się wykonał to w triggerze nic nie rób.

Dopisek:
Jeżeli chcesz mieć unikalne wartości to zrób unique key na parę kolumn, po gwizdek trigger? :/

0
  1. Co do podwójnego inserta to wiem, jestem laikiem w temacie pl/sql, ale spróbowałem też odpalić trigger bez wewnętrznego inserta i dalej występuje ten sam problem.
  2. Też może być, a dlaczego nie? :D

Aktualny kod:

CREATE OR REPLACE TRIGGER czy_juz_istnieje
  before INSERT ON VOTES
  FOR each ROW
    DECLARE
    id_uzytkownika NUMBER(10,0) := :NEW.vote_userID;
    id_odpowiedzi NUMBER(10,0) := :NEW.vote_answerID; 
    istnieje VARCHAR2(1 CHAR) := 0;
 
    BEGIN
    -- jesli istnieje, zmienna istnieje wynosi 1:
      SELECT 1 INTO istnieje FROM VOTES WHERE VOTE_ANSWERID = id_odpowiedzi AND VOTE_USERID = id_uzytkownika;
    IF istnieje > 0
    THEN
     RAISE_APPLICATION_ERROR(-20666, 'JUŻ GŁOSOWAŁEŚ!');
    END IF;
 
END;
 
SET transaction name 'aaa';
INSERT INTO VOTES VALUES (1,22);
COMMIT;
0

Pewnie Ci się select wywala
zamiast select 1 sprawdź:
SELECT COUNT(*) INTO istnieje FROM...
i zamien zmienna istnieje na number

0

Kolega zenek_s dobrze radzi, ale tu jest jeszcze inny problem - odwołujesz się w wyzwalaczu do tabeli, która spowodowała odpalenie tego wyzwalacza - jest to w tej sytuacji tzw. tabela mutująca - jednowierszowy insert nie spowoduje błędu, natomiast każdy wielowierszowy insert lub jakikolwiek update wywali błąd - rozwiązaniem są tzw. wyzwalacze złożone, choć w tym przypadku wystarczy zrobić unique key - baza sama sprawdzi unikalność danych.

0
Pablitto77 napisał(a):

Kolega zenek_s dobrze radzi

/ciach/
Jak to ma być dobra rada, to ja przepraszam...
Nie COUNT do jasnej cholery, tylko if exists jeśli już.
A o różnicach i dlaczego tak, to już sobie doczytajcie...
Albo i nie czytajcie, a wtedy możecie również powiedzieć, że "nie każda tabela musi posiadać klucz główny".

Pablitto77 napisał(a):

[...] ale tu jest jeszcze inny problem - odwołujesz się w wyzwalaczu do tabeli, która spowodowała odpalenie tego wyzwalacza - jest to w tej sytuacji tzw. tabela mutująca - jednowierszowy insert nie spowoduje błędu, natomiast każdy wielowierszowy insert lub jakikolwiek update wywali błąd

A zauważyłeś tam magiczne for each row?
Jakim cudem ma to się wywalić na "każdy wielowierszowy insert lub jakikolwiek update wywali błąd"?
Problem jest gdzie indziej, imho.

Nie jestem biegły w tym dialekcie SQL, ale ja bym napisał to tak:

CREATE OR REPLACE TRIGGER czy_juz_istnieje
  before INSERT ON VOTES FOR each ROW
  BEGIN
    IF exists(SELECT * FROM VOTES WHERE VOTE_ANSWERID = :NEW.VOTE_ANSWERID AND VOTE_USERID = :NEW.VOTE_USERID) THEN
      RAISE_APPLICATION_ERROR(-20666, 'JUŻ GŁOSOWAŁEŚ!');
    END IF;
  END;
1
wloochacz napisał(a):
Pablitto77 napisał(a):

Kolega zenek_s dobrze radzi

/ciach/
Jak to ma być dobra rada, to ja przepraszam...

Nie jestem biegły w tym dialekcie SQL, ale ja bym napisał to tak:

CREATE OR REPLACE TRIGGER czy_juz_istnieje
  before INSERT ON VOTES FOR each ROW
  BEGIN
    IF exists(SELECT * FROM VOTES WHERE VOTE_ANSWERID = :NEW.VOTE_ANSWERID AND VOTE_USERID = :NEW.VOTE_USERID) THEN
      RAISE_APPLICATION_ERROR(-20666, 'JUŻ GŁOSOWAŁEŚ!');
    END IF;
  END;

EXISTS można używać tylko w zapytaniu więc nie zrobisz w PL/SQL if exists
Jak już chcemy być tacy dokładni to możemy łapać wyjątek NO_DATA_FOUND z tego selecta

 
BEGIN
 SELECT 1 INTO istnieje FROM VOTES WHERE VOTE_ANSWERID = :NEW.VOTE_ANSWERID AND VOTE_USERID = :NEW.VOTE_USERID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  istnieje := 0;
END;

lub można też użyć case:

 select case when exists(select * from VOTES where VOTE_ANSWERID = :NEW.VOTE_ANSWERID and VOTE_USERID = :NEW.VOTE_USERID) then 1
else 0
end 
into istnieje from dual; 

Ale dobrym pomysłem jest założenie klucza na obie te kolumny

0
zenek_s napisał(a):

EXISTS można używać tylko w zapytaniu więc nie zrobisz w PL/SQL if exists
Jak już chcemy być tacy dokładni to możemy łapać wyjątek NO_DATA_FOUND z tego selecta

Naprawdę? Nie chce mi się w to wierzyć, bo niby czemu nie miałoby to zadziałać skoro exists zwraca true/false i jest absolutnie poprawnym wyrażeniem logicznym?

Sprawdziłem... Cóż, nie widziałem o tym ;-)
Ale zgodzisz się, że to durnowate jest co nieco, prawda?
Tylko co z tego, że durnowate - tak jest i koniec.

Co do tego czy się nie da; da się, ale na około przez użycie exists w where (pokręcony ten Oracle...):

select count(*) 
  from dual 
where exists (select NULL from foo where bar) 
0
wloochacz napisał(a):
Pablitto77 napisał(a):

Kolega zenek_s dobrze radzi

/ciach/
Jak to ma być dobra rada, to ja przepraszam...
Nie COUNT do jasnej cholery, tylko if exists jeśli już.
A o różnicach i dlaczego tak, to już sobie doczytajcie...
Albo i nie czytajcie, a wtedy możecie również powiedzieć, że "nie każda tabela musi posiadać klucz główny".

Pablitto77 napisał(a):

[...] ale tu jest jeszcze inny problem - odwołujesz się w wyzwalaczu do tabeli, która spowodowała odpalenie tego wyzwalacza - jest to w tej sytuacji tzw. tabela

mutująca - jednowierszowy insert nie spowoduje błędu, natomiast każdy wielowierszowy insert lub jakikolwiek update wywali błąd

A zauważyłeś tam magiczne for each row?
Jakim cudem ma to się wywalić na "każdy wielowierszowy insert lub jakikolwiek update wywali błąd"?
Problem jest gdzie indziej, imho.

</quote>

Kolego włochacz dokładnie zauważyłem for each row - i właśnie problem tabeli mutującej dotyczy wyzwalaczy wiersza, zatem to nie cud tylko ktoś tak W Oracle to zaprojektował - wszystkie instrukcje DML na tabelach mutujących wywalą błąd (oczywiście jeśli wyzwalacz dotyczy wszystkich tych instrukcji) przykładowo
dla triggera
BEFORE INSERT OR UPDATE
błąd wystąpi dla każdej instrukckcji update i każdego wielowierszowego inserta (jednowierszowy jest wyjątkiem)
odsyłam do źródeł, tam masz też inny przykład
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS759

a tu też nie rozumiem - a czemu nie SELECT COUNT(*) INTO ?, zliczasz sobie wystąpienia według warunku i jeśli jest > 0 podnosisz błąd.
to z powodzeniem zadziała:

CREATE OR REPLACE TRIGGER czy_juz_istnieje
  before INSERT ON VOTES FOR each ROW
DECLARE
n NUMBER (5);
  BEGIN
    SELECT COUNT(*) INTO n FROM VOTES WHERE VOTE_ANSWERID = :NEW.vote_answerID AND VOTE_USERID = :NEW.vote_userID;
    IF n > 0 THEN
      RAISE_APPLICATION_ERROR(-20666, 'JUŻ GŁOSOWAŁEŚ!');
    END IF;
  END;

,ale tak jak pisałem dla inserta jednowierszowego, dla wielowierszowego wywali błąd tabeli mutującej.

1

Takie podejście nie ma sensu. Wystarczy że dwie sesje wbiją z tymi samymi danymi w tym samym momencie. Pierwsza nie zdąży zrobić commit przed odpytaniem o istnienie przez drugą i cało to sprawdzanie idzie do piachu.

Druga sprawa - wydajność. I tak trzeba będzie założyć indeks na odpytywanych polach aby to jako tako działało, więc jak już indeks to zrobić go unikalnym i po problemie.

0
juro napisał(a):

Takie podejście nie ma sensu. Wystarczy że dwie sesje wbiją z tymi samymi danymi w tym samym momencie. Pierwsza nie zdąży zrobić commit przed odpytaniem o istnienie przez drugą i cało to sprawdzanie idzie do piachu.

Jesteśmy w triggerze, trigger wchodzi w transakcje inserta, więc oracle powinien zapewnić atomowość obu transakcji

0

Mały struktura sprawdzająca:

 

create table tmp_tab(
id_user number,
vote varchar2(200)
);

CREATE OR REPLACE TRIGGER trg_tmp_tab
BEFORE INSERT ON tmp_tab FOR each ROW
DECLARE
n NUMBER (5);
  BEGIN
    SELECT COUNT(*) INTO n FROM tmp_tab WHERE VOTE=:NEW.vote AND id_user = :NEW.id_user;
    IF n > 0 THEN
      RAISE_APPLICATION_ERROR(-20666, 'JUŻ GŁOSOWAŁEŚ!');
    END IF;
END;
/

Robimy w jednej i drugiej sesji bez commit:

 insert into tmp_tab values(1,2);

następnie dajemy commit w obydwóch - błędu nie będzie, pojawi się dopiero przy próbie następnego wstawienia takich danych.

0

Takie rzeczy to się ogranicza kluczem unikalnym...

0

@juro, ale temat raczej nie dotyczy wielodostępu i izolacji transakcji bo to osobna bajka i autor o to nie pytał.

dywagujemy tu sobie trochę nie znając do końca założeń - jeśli to ma być zwykłe sprawdzenie, co jest najbardziej prawdopodobne czy dany delikwent głosował tak/nie - czyli został wpisany (na wzór prawdziwych wyborów) do karty głosowań (tu id_user do tabeli Votes) - to jak już się wszyscy tu zgodziliśmy wystarczy założyć unique key na kolumnie id_user i po kłopocie - baza sprawdzi to najwydajniej, najbezpieczniej sama .
My natomiast tego na pewno nie wiemy - być może autorowi chodziło o sprawdzenie czy czy dany user głosował w dany sposób ?

0

Przykład z życia - gdy idziesz do głosowania, to NAJPIERW jest sprawdzane, czy już nie głosowałeś, potem dostajesz kartę do głosowania, a na samym końcu oddajesz głos.
Nie odwrotnie - w momencie wrzucania do urny komisja ci mówi - "hola, hola, Pan juz głosował"

0

tak, tak dokładnie o to mi chodziło - źle się trochę wyraziłem pisząc karty głosowań miałem na myśli tą wielką księgę, gdzie kwitujesz pobranie karty do głosowania (czyli tej w której stawiamy X ) :)

0
Marcin.Miga napisał(a):

Takie rzeczy to się ogranicza kluczem unikalnym...

Tak masz racje już wcześniej się z tym zgodziliśmy, tutaj dyskusja toczy się już nad samą ideą zaprogramowania triggera, co myślę jest dużo ciekawszym i rozwijającym tematem, niż samo założenie klucza (Chociaż założenie klucza jest rozwiązaniem problemu autora).

Co do wypowiedzi @juro, sprawdziłem, tak masz rację w takiej sytuacji będziemy mieli podwójny wpis i nasze sprawdzanie nie zadziała. Chociaż można to rozwiązać stosując np instrukcję LOCK TABLE przed selectem, ale nie jest to efektywne rozwiązanie jeśli nasza tabela jest często selectowana.

1 użytkowników online, w tym zalogowanych: 0, gości: 1