[PL/SQL] Triggery zawierające pola z różnych tabel

0

Witam, posiadam bazę danych wypożyczalni samochodów (projekt na zaliczenie), potrzebuje zrobić trigger który będzie obliczał mi koszt wypożyczenia na podstawie ilości dni oraz roku produkcji samochodu (im starszy tym taniej), problem w tym, że rok produkcji posiadam w tabeli SAMOCHODY natomiast dni wypożyczenia oraz koszt w tabeli WYPOŻYCZENIA, może mi ktoś powiedzieć jak mogę pobrać dane z jednej tabeli do drugiej? Próbowałem coś takiego ale z oczywistych powodów to nie działa i oracle wywala błąd niepoprawnej zmiennej wiązania.

CREATE OR REPLACE TRIGGER KOSZT_WYPO 
BEFORE INSERT OR UPDATE ON WYPOZYCZENIA 
FOR EACH ROW 
BEGIN
  if(:new.ROK_PRODUKCJI <= 1995) then :new.KOSZT_WYPOZYCZENIA := :new.ILOSC_DNI*100; end if;
  if(:new.ROK_PRODUKCJI >= 1996 and :new.ROK_PRODUKCJI <=2010) then :new.KOSZT_WYPOYCZENIA :=new.ILOSC_DNI*500; end if;
  if(:new.ROK_PRODUKCJI >= 2011 and :new.ROK_PRODUKCJI <=2018) then :new.KOSZT_WYPOZYCZENIA :=new.ILOSC_dni*1000; end if;
END;

Dodam, że jestem zielony jeśli chodzi o pl/sql i w tym projekcie na każdym kroku napotykam problem więc większa pomoc jest mile widziana.

0

no selectem a jak inaczej? SELECT pole INTO zmienna FROM tabela WHERE inne_pole = :new.jakies_inne_pole

0

Niestety nie bardzo rozumiem jak mam to wykorzystać w mojej funkcji, selecta zamieścić bezpośrednio w IF'ie czy przed?

0
  1. deklarujesz zmienną
  2. robisz selecta into zmienna i masz w zmienna wartość z innej tabeli (select musi zwrócić jeden rekord, wypadało by się zabezpieczyć na wypadek braku rekordu w tabeli z samochodami)
  3. to co potrzebujesz jest nie w :new tylko w zmienna
0

Okej, zrobiłem to w taki sposób i działa:

CREATE OR REPLACE TRIGGER KOSZT_WYPO 
BEFORE INSERT OR UPDATE ON WYPOZYCZENIA 
FOR EACH ROW 
DECLARE

rok_prod float;

BEGIN
SELECT ROK_PRODUKCJI INTO rok_prod FROM SAMOCHODY WHERE ID_SAMOCHODU = :new.ID_SAMOCHODU;
 IF rok_prod <= 1995 THEN :new.KOSZT_WYPOZYCZENIA := :new.ILOSC_DNI*50;
 ELSIF rok_prod >=1996 AND rok_prod <= 2005 THEN :new.KOSZT_WYPOZYCZENIA := :new.ILOSC_DNI*250;
 ELSIF rok_prod >=2006 AND rok_prod <= 2015 THEN :new.KOSZT_WYPOZYCZENIA := :new.ILOSC_DNI*450;
 ELSIF rok_prod >=2016 THEN :new.KOSZT_WYPOZYCZENIA := :new.ILOSC_DNI*650;
END IF;
END;

Następny problem mam z obliczeniami na datach, otóż potrzebuję obliczyć liczbę lat jaka minęła od zatrudnienia pracownika do daty dzisiejszej, do tej pory udało mi się sklecić coś takiego:

CREATE OR REPLACE TRIGGER PRACOWNICY_WYNAGRO 
BEFORE INSERT OR UPDATE ON PRACOWNICY 
FOR EACH ROW
DECLARE
data_z float;

BEGIN
select (((select sysdate from dual)- DATA_ZATRUDNIENIA)/365) into data_z from PRACOWNICY ;
if data_z <=2 then :new.BONUS := :new.WYNAGRODZENIE*0.15;
end if;
END;

CAŁOŚĆ się kompiluje ale przy próbie dodania nowego rekordu wyskakuje mi błąd:

One error saving changes to table "PAWEL"."PRACOWNICY":
Row 6: ORA-01422: dokładne pobranie zwraca większą liczbę wierszy niż zamówiono
ORA-06512: przy "PAWEL.PRACOWNICY_WYNAGRO", linia 7
ORA-04088: błąd w trakcie wykonywania wyzwalacza 'PAWEL.PRACOWNICY_WYNAGRO'
ORA-06512: przy linia 1<

Natomiast jak wyczyściłem tabele PRACOWNICY to przy próbie dodania nowego rekordu otrzymuję błąd:

One error saving changes to table "PAWEL"."PRACOWNICY":
Row 1: ORA-01403: nie znaleziono danych
ORA-01403: nie znaleziono danych
ORA-06512: przy "PAWEL.PRACOWNICY_WYNAGRO", linia 7
ORA-04088: błąd w trakcie wykonywania wyzwalacza 'PAWEL.PRACOWNICY_WYNAGRO'
ORA-06512: przy linia 1<

0

pierwsze to to SELECT ROK_PRODUKCJI INTO rok_prod FROM SAMOCHODY WHERE ID_SAMOCHODU = :NEW.ID_SAMOCHODU; zwraca więcej niż jeden rekord, a drugie to to samo zapytanie nie zwraca nic. Co do pierwszego to podejrzewam błąd w danych, to drugie to np. https://community.oracle.com/thread/1036850

0

Czyli odnośnie tego pierwszego selecta to jak to powinno być? Bo teraz jak testowałem to działa teoretycznie poprawnie.

1

jeśli masz tabelę samochody, w które jest pole id_samochodu to logiczne jest, że jest to pole UNIKALNE. Więc nie powinno być sytuacji, że dla jednego ID masz wiele rekordów.

0

No dobrze, a odnośnie tego:

CREATE OR REPLACE TRIGGER PRACOWNICY_WYNAGRO 
BEFORE INSERT OR UPDATE ON PRACOWNICY 
FOR EACH ROW
DECLARE
data_z FLOAT;
 
BEGIN
SELECT (((SELECT SYSDATE FROM dual)- DATA_ZATRUDNIENIA)/365) INTO data_z FROM PRACOWNICY ;
IF data_z <=2 THEN :NEW.BONUS := :NEW.WYNAGRODZENIE*0.15;
END IF;
END;

Co zrobić żeby to działało? Jak to przerobić?

0

@xDestiXx: Brakuje ci WHERE w tym zapytaniu. Przecież nie pobierasz dat zatrudnienia dla wszystkich pracowników, tylko dla jednego...

0

W sumie nawet nie zauważyłem, teraz poprawiłem i wygląda to tak:

select (((select sysdate from dual)- DATA_ZATRUDNIENIA)/365) into data_z from PRACOWNICY where ID_PRACOWNIKA = :new.ID_PRACOWNIKA;

No ale nadal pojawia się błąd:

One error saving changes to table "PAWEL"."PRACOWNICY":
Row 4: ORA-01403: nie znaleziono danych
ORA-01403: nie znaleziono danych
ORA-06512: przy "PAWEL.PRACOWNICY_WYNAGRO", linia 5
ORA-04088: błąd w trakcie wykonywania wyzwalacza 'PAWEL.PRACOWNICY_WYNAGRO'
ORA-06512: przy linia 1<

Z czym to może być związane? Obstawiam, że nadal problem jest z selectem. Nie znaleziono danych, ale jakich?

0

To jest logiczne, że przy INSERT nie będziesz miał w tabeli jeszcze tego pracownika, więc musisz DATA_ZATRUDNIENIA pobrać z NEW. Właściwie przy UPDATE również, tyle że nie wiem, czy pracownik byłby zadowolony, gdyby mu się bonus zmienił przez błąd pani wprowadzającej...

0

No dobrze, czyli jak mam w selekcie SELECT sysdate FROM dual)- DATA_ZATRUDNIENIA to zmieniam to na SELECT sysdate FROM dual)- :new.DATA_ZATRUDNIENIA i to wszystko? Jeśli tak to nadal nie działa... Jeśli można to prosiłbym o podanie poprawnej budowy selecta gdzie będzie obliczana różnica dat od daty zatrudnienia (DATA_ZATRUDNIENIA) do daty dzisiejszej.

0
IF ((SELECT SYSDATE FROM dual)- :NEW.DATA_ZATRUDNIENIA)/365 <=2 THEN :NEW.BONUS := :NEW.WYNAGRODZENIE*0.15;
0

screenshot-20180830144255.png

0

Może i można to zapisać na inne sposoby ale nie ogarniam tego, bazy danych to nie moja bajka...

BEGIN
SELECT :new.DATA_ZATRUDNIENIA INTO data_z FROM PRACOWNICY WHERE ID_PRACOWNIKA =:new.ID_PRACOWNIKA;
SELECT SYSDATE INTO sysd FROM DUAL;

IF (( sysd-data_z)/365) <=2 THEN :NEW.BONUS := :NEW.WYNAGRODZENIE*0.15;
end if;
END;

To też nie działa, o ile trigger się kompiluje to przy próbie dodania rekordu ten sam błąd co wcześniej.

0

Nadal kombinuje i nic dobrego z tego nie wychodzi, nie mniej jednak udało mi się stworzyć coś takiego:

create or replace 
trigger PRACOWNICY_WYNAGRO 
BEFORE INSERT OR UPDATE ON PRACOWNICY 
FOR EACH ROW
DECLARE

wynik FLOAT;

BEGIN
SELECT MONTHS_BETWEEN (sysdate, :new.DATA_ZATRUDNIENIA)/12 into wynik FROM PRACOWNICY WHERE ID_PRACOWNIKA = '20';
IF wynik <=2 THEN :new.BONUS := :new.WYNAGRODZENIE*0;
ELSIF wynik >2 and wynik <=5 then :new.BONUS := :new.WYNAGRODZENIE*0.20; 
ELSIF wynik >5 and wynik <=8 then :new.BONUS := :new.WYNAGRODZENIE*0.30;
ELSIF wynik >8 and wynik <=10 then :new.BONUS := :new.WYNAGRODZENIE*0.40;
ELSIF wynik >10 then :new.BONUS := :new.WYNAGRODZENIE*0.50;
end if;
END;

i działa to dla każdego nowego rekordu i oblicz bonus po dacie tego dodawanego rekordu mimo, że zamiast "WHERE ID_PRACOWNIKA = :new.ID_PRACOWNIKA" mam wpisane "='20'".

Ktoś mi to wyjaśni?

1

DizłąDziała ci dlatego, że pobierasz z tablei pracownicy DOKŁADNIE jeden rekord (o ID=20), o ile takowy posiadasz. W samym SELECT NIE ODWOŁUJESZ SIĘ DO ŻADNEGO pola z tego rekordu. Będzie ci to działać, dopóki nie usuniesz rekordu o ID=20. Powinieneś brać z DUAL.

1

powinno być

wynik := MONTHS_BETWEEN (SYSDATE, :NEW.DATA_ZATRUDNIENIA)/12;

0

Okej, bardzo wam dziękuje za pomoc, wszystko teraz działa jak należy.

0

Mam jeszcze jedno pytanie, otóż chcę zapobiec wypożyczeniu jednego auta dwóm różnym osobom w tym samym czasie i zastanawiam się czy zrobić to w aplikacji czy bezpośrednio w bazie. Myślałem nad rozwiązaniem typu, w momencie wypożyczenia przy danym rekordzie w kolumnie WYPOŻYCZONY pojawia się słowo "TAK" i potem w aplikacji sprawdzę jakie tam jest słowo, jeśli tak to nie wypożyczy a jeśli nie to wypożyczy. Pytanie brzmi następująco, czy da się jakoś w plsql zrobić trigger/funkcję albo cokolwiek, która automatycznie określonego dnia zmieni wartość z TAK na NIE? Czy może jest jakiś inny lepszy sposób żeby zrobić takie zabezpieczenie?

0

A co jeśli klient zwróci samochód przed terminem albo zwróci kilka dni po? Wtedy albo nie będziesz go mógł wypożyczyć mimo iż będzie dostępny albo przeciwnie - mimo iż go nie będzie system będzie pokazywał, że jest dostępny. Przy zwrocie (i dopiero wtedy) powinieneś zapisywać tą informację do bazy.

0

Faktycznie nie przemyślałem tego, czyli ręcznie trzeba będzie wpisywać dostępność. A jak już ręcznie wpiszę dostępność to lepiej sprawdzać to przy pomocy np triggera czy lepiej zrobić bezpośrednio w aplikacji takie zabezpieczenie?

0

Wg mnie powinieneś to sprawdzać tu i tu, tzn. na wstępie odfiltrować samochody, które już są wypożyczone ale przy próbie zapisu (wypożyczenia) sprawdzić czy samochód nadal jest dostępny (inny pracownik mógł go wypożyczyć bo klient zastanawiał się krócej). To chyba lepiej zrobić na poziomie bazy np. triggerem

0

A mógłbyś przygotować jakiś przykład jak to powinno mniej więcej wyglądać? Bo na moją logikę musiałbym zrobić selecta który pobrałby samochód o danym ID i sprawdził czy pole WYPOŻYCZONY zawiera słowo "tak" a jeśli zawiera słowo "tak" to co dalej? Tutaj już nie bardzo wiem jak to napisać.

1

ale co jak powinno wyglądać? Masz pole w tabeli z samochodami dostepny i przy próbie zmiany rekordu (trigger before update) sprawdzasz czy stara wartość pola jest równa TAK. Jeśli jest inna to trzeba rzucić wyjątkiem https://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html RAISE EXCEPTION i złapać go po stronie aplikacji.

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