Created
June 13, 2011 11:47
-
-
Save mingan/1022655 to your computer and use it in GitHub Desktop.
Ukázkový trigger
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE TRIGGER "tib_je_polozkou" before insert | |
on JE_POLOZKOU for each row | |
declare | |
EX_NEKOMP_POB EXCEPTION; | |
EX_KOLO_JE_PUJC EXCEPTION; | |
EX_UZAVR_VYPUJC EXCEPTION; | |
volne integer; | |
uzavreno TIMESTAMP; | |
pobockaVyp integer; | |
pobockaKol integer; | |
begin | |
-- kontrola, zda kolo není půjčeno | |
SELECT COUNT(*) INTO volne | |
FROM VOLNA_KOLA | |
WHERE CIS_KOLA = :new.CIS_KOLA; | |
if (volne = 0) then | |
raise EX_KOLO_JE_PUJC; | |
end if; | |
-- kontrola, že nejde o uzavřenou výpůjčku | |
SELECT DATUM_VRACENI INTO uzavreno | |
FROM VYPUJCKA | |
WHERE CIS_VYP = :new.CIS_VYP; | |
if (uzavreno IS NOT NULL) then | |
raise EX_UZAVR_VYPUJC; | |
end if; | |
-- kolo musí být z pobočky, které patří výpůjčka | |
SELECT CIS_POB INTO pobockaVyp FROM VYPUJCKA WHERE CIS_VYP = :new.CIS_VYP; | |
SELECT CIS_POB INTO pobockaKol FROM KOLO WHERE CIS_KOLA = :new.CIS_KOLA; | |
if (pobockaVyp != pobockaKol) then | |
raise EX_NEKOMP_POB; | |
end if; | |
-- Errors handling | |
exception | |
WHEN EX_NEKOMP_POB THEN | |
RAISE_APPLICATION_ERROR(-20006, 'Křížení poboček, zaměstnanců a kol'); | |
ROLLBACK; | |
WHEN EX_KOLO_JE_PUJC THEN | |
RAISE_APPLICATION_ERROR(-20006, 'Nelze půjčit kolo, když je půjčeno'); | |
ROLLBACK; | |
WHEN EX_UZAVR_VYPUJC THEN | |
RAISE_APPLICATION_ERROR(-20005, 'Nelze měnit, výpůjčka je již uzavřena'); | |
ROLLBACK; | |
WHEN OTHERS THEN | |
RAISE_APPLICATION_ERROR(-20100, 'Odkazujete se na neexistující řádek.'); | |
ROLLBACK; | |
end; | |
/ | |
ALTER TRIGGER "tib_je_polozkou" ENABLE; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
GRANT SELECT ON JE_POLOZKOU TO STUDENT; | |
GRANT SELECT ON KOLO TO STUDENT; | |
GRANT SELECT ON POBOCKA TO STUDENT; | |
GRANT SELECT ON TYP_KOLA TO STUDENT; | |
GRANT SELECT ON VYPUJCKA TO STUDENT; | |
GRANT SELECT ON ZAKAZNIK TO STUDENT; | |
GRANT SELECT ON ZAMESTNANEC TO STUDENT; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON JE_POLOZKOU TO IT218; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON KOLO TO IT218; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON POBOCKA TO IT218; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON TYP_KOLA TO IT218; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON VYPUJCKA TO IT218; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON ZAKAZNIK TO IT218; | |
GRANT SELECT, INSERT, UPDATE, DELETE ON ZAMESTNANEC TO IT218; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE TRIGGER "tib_let" before insert | |
on LET for each row | |
declare | |
EX_CASOVA_SOUSLEDNOST EXCEPTION; | |
EX_NENI_PILOT EXCEPTION; | |
EX_SPATNA_TRIDA EXCEPTION; | |
EX_SPATNY_ROCNIK EXCEPTION; | |
jePilot INTEGER; | |
tridaKluzaku CHAR(8); | |
rokStartu YEAR; | |
rokPristani YEAR; | |
begin | |
-- datum přistání následuje po datu startu | |
if (:NEW.DATUM_S >= :NEW.DATUM_P) then | |
raise EX_CASOVA_SOUSLEDNOST; | |
end if; | |
-- datum přistání není v budoucnosti | |
if (:NEW.DATUM_P >= sysdate) then | |
raise EX_CASOVA_SOUSLEDNOST; | |
end if; | |
-- ident osoby je ident pilota | |
SELECT COUNT(:NEW.IDENT) into jePilot FROM PILOT; | |
if (jePilot = 0) then | |
raise EX_NENI_PILOT; | |
end if; | |
-- kontrola, že třída kluzáku je jednou ze tříd soutěže | |
SELECT COUNT(NAZEV) into tridaKluzaku FROM IDENT WHERE ROCNIK = :NEW.ROCNIK AND NAZEV = | |
(SELECT NAZEV FROM KLUZAK WHERE MODEL = :NEW.MODEL) | |
if (tridaKluzaku = 0) then | |
raise EX_SPATNA_TRIDA; | |
end if; | |
-- datum startu i přistání jsou v roce, na který je soutěž vypsána | |
SELECT YEAR(:NEW.DATUM_S) INTO rokStartu; | |
SELECT YEAR(:NEW.DATUM_P) INTO rokPristani; | |
if (not(rokStartu = rokPristani = :NEW.ROCNIK)) then | |
raise EX_SPATNY_ROCNIK; | |
end if; | |
-- Errors handling | |
exception | |
WHEN EX_CASOVA_SOUSLEDNOST THEN | |
RAISE_APPLICATION_ERROR(-20001, 'Data musí následovat logicky po sobě'); | |
ROLLBACK; | |
WHEN EX_NENI_PILOT THEN | |
RAISE_APPLICATION_ERROR(-20002, 'Není pilot'); | |
ROLLBACK; | |
WHEN EX_SPATNA_TRIDA THEN | |
RAISE_APPLICATION_ERROR(-20003, 'Kluzák nepatří do třídy, pro níž je soutěž vypsána'); | |
ROLLBACK; | |
WHEN EX_SPATNY_ROCNIK THEN | |
RAISE_APPLICATION_ERROR(-20004, 'Let se neuskutečnil v roce soutěže'); | |
ROLLBACK; | |
WHEN OTHERS THEN | |
RAISE_APPLICATION_ERROR(-20100, 'Nedefinovaná chyba'); | |
ROLLBACK; | |
end; | |
/ | |
ALTER TRIGGER "tib_let" ENABLE; | |
CREATE OR REPLACE TRIGGER "tiub_soutez" before insert, update | |
on SOUTEZ for each row | |
declare | |
EX_CASOVA_SOUSLEDNOST EXCEPTION; | |
begin | |
if (:NEW.ROCNIK < TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))) then | |
raise EX_SOUTEZ_V_BUD; | |
end if; | |
-- Errors handling | |
exception | |
WHEN EX_CASOVA_SOUSLEDNOST THEN | |
RAISE_APPLICATION_ERROR(-20001, 'Data musí následovat logicky po sobě'); | |
ROLLBACK; | |
WHEN OTHERS THEN | |
RAISE_APPLICATION_ERROR(-20100, 'Nedefinovaná chyba'); | |
ROLLBACK; | |
end; | |
/ | |
ALTER TRIGGER "tib_let" ENABLE; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment