Trigger létrehozása

1. példa

/*
A következő trigger segítségével a kölcsönzés
adminisztrácója automatizálható.
Ugyanis egyetlen sor beszúrása a kolcsonzes táblába
maga után vonja az ugyfel és a konyv táblák megfelelő
bejegyzéseinek változtatását.
A trigger sor szintű. Ha nem lehet a kölcsönzést
végrehajtani, kivételt dobunk.
*/
CREATE OR REPLACE TRIGGER tr_insert_kolcsonzes
  AFTER INSERT ON kolcsonzes
  FOR EACH ROW
DECLARE
  v_Ugyfel ugyfel%ROWTYPE;
BEGIN
  SELECT * INTO v_Ugyfel
  FROM ugyfel WHERE id = :NEW.kolcsonzo;
  IF v_Ugyfel.max_konyv = v_Ugyfel.konyvek.COUNT THEN
    RAISE_APPLICATION_ERROR(-20010,
    v_Ugyfel.nev || ' ügyfél nem kölcsönözhet több könyvet.');
  END IF;
  INSERT INTO TABLE(SELECT konyvek FROM ugyfel
  WHERE id = :NEW.kolcsonzo)
  VALUES (:NEW.konyv, :NEW.datum);
  BEGIN
  UPDATE konyv SET szabad = szabad -1
  WHERE id = :NEW.konyv;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020,
        'Nincs a könyvből több példány.');
  END;
END tr_insert_kolcsonzes;
/
show errors

/* Nézzünk néhány példát.
A példák az inicializált adatbázis adatain futnak. */

/*
József István és az 'SQL:1999 ...' könyv
Sajnos az ügyfél nem kölcsönözhet többet.
*/
INSERT INTO kolcsonzes (kolcsonzo, konyv, datum)
VALUES (15, 30, SYSDATE);

/*
Hiba a(z) 1. sorban:
INSERT INTO kolcsonzes (kolcsonzo, konyv, datum)
*
ORA-20010: József István ügyfél nem kölcsönözhet több könyvet.
ORA-06512: a(z) "PLSQL.TR_INSERT_KOLCSONZES", helyen a(z) 8. sornál
ORA-04088: hiba a(z) 'PLSQL.TR_INSERT_KOLCSONZES' trigger futása közben
*/

/*
Komor Ágnes és az 'A critical introduction… ' könyv
Sajnos a könyvből nincs szabad példány.
*/
INSERT INTO kolcsonzes (kolcsonzo, konyv, datum)
VALUES (30, 35, SYSDATE);

/*
Hiba a(z) 1. sorban:
INSERT INTO kolcsonzes (kolcsonzo, konyv, datum)
*
ORA-20020: Nincs a könyvből több példány.
ORA-06512: a(z) "PLSQL.TR_INSERT_KOLCSONZES", helyen a(z) 21. sornál
ORA-04088: hiba a(z) 'PLSQL.TR_INSERT_KOLCSONZES' trigger futása közben
*/

/*
Komor Ágnes és a 'The Norton Anthology… ' könyv
Minden rendben lesz.
*/
INSERT INTO kolcsonzes (kolcsonzo, konyv, datum)
VALUES (30, 40, SYSDATE);
SELECT * FROM TABLE(SELECT konyvek FROM ugyfel
WHERE id = 30);

/*
1 sor létrejött.
KONYV_ID    DATUM
---------- -----------
         5 02-ÁPR. 12
        10 02-MÁRC. 12
        40 02-MÁJ. 12
*/

2. példa

/* A következő trigger segítségével naplózzuk
a törölt kölcsönzéseket a kolcsonzes_naplo táblában. */
CREATE OR REPLACE TRIGGER tr_kolcsonzes_naploz
  AFTER DELETE ON kolcsonzes
  REFERENCING OLD AS kolcsonzes
  FOR EACH ROW
DECLARE
  v_Konyv konyv%ROWTYPE;
  v_Ugyfel ugyfel%ROWTYPE;
BEGIN
  SELECT * INTO v_Ugyfel
  FROM ugyfel WHERE id = :kolcsonzes.kolcsonzo;
  SELECT * INTO v_Konyv
  FROM konyv WHERE id = :kolcsonzes.konyv;
  INSERT INTO kolcsonzes_naplo VALUES(
  v_Konyv.ISBN, v_Konyv.cim,
  v_Ugyfel.nev, v_Ugyfel.anyja_neve,
  :kolcsonzes.datum, SYSDATE,
  :kolcsonzes.megjegyzes);
END tr_kolcsonzes_naploz;
/
show errors

3. példa

/* A következő trigger megakadályozza, hogy
egy könyvet 2-nél többször meghosszabítsanak.
Megjegyzés:
- A WHEN feltételében nem kell ':' az OLD, NEW, PARENT elé.
- Ugyanez a hatás elérhető egy CHECK megszorítással. */
CREATE OR REPLACE TRIGGER tr_kolcsonzes_hosszabbit
  BEFORE INSERT OR UPDATE ON kolcsonzes
  FOR EACH ROW
  WHEN (NEW.hosszabbitva > 2 OR NEW.hosszabbitva < 0)
BEGIN
  RAISE_APPLICATION_ERROR(-20005,
    'Nem megengedett a hosszabbítások száma');
END tr_kolcsonzes_hosszabbit;
/
show errors

UPDATE kolcsonzes SET hosszabbitva = 10;

/*
Hiba a(z) 1. sorban:
UPDATE kolcsonzes SET hosszabbitva = 10
*
ORA-20005: Nem megengedett a hosszabbítások száma
ORA-06512: a(z) "PLSQL.TR_KOLCSONZES_HOSSZABBIT", helyen a(z) 2. sornál
ORA-04088: hiba a(z) 'PLSQL.TR_KOLCSONZES_HOSSZABBIT' trigger futása közben
*/

4. példa

/* Egy triggerrel megakadályozzuk azt, hogy valaki a kolcsonzes_naplo
táblából töröljön, vagy az ott levő adatokat módosítsa.
Nincs szükség sor szintű triggerre, elég utasítás szintű trigger. */
CREATE OR REPLACE TRIGGER tr_kolcsonzes_naplo_del_upd
  BEFORE DELETE OR UPDATE ON kolcsonzes_naplo
BEGIN
  RAISE_APPLICATION_ERROR(-20100,
    'Nem megengedett művelet a kolcsonzes_naplo táblán');
END tr_kolcsonzes_naplo_del_upd;
/

5. példa

/* A NEW pszeudováltozó értéke megváltoztatható BEFORE triggerben,
és akkor az új érték kerül be a táblába. */
CREATE TABLE szam_tabla(a NUMBER);

CREATE OR REPLACE TRIGGER tr_duplaz
  BEFORE INSERT ON szam_tabla
  FOR EACH ROW
BEGIN
  :NEW.a := :NEW.a * 2;
END tr_duplaz;
/

INSERT INTO szam_tabla VALUES(5);

SELECT * FROM szam_tabla;

/*
A
----------
10
*/

DROP TRIGGER tr_duplaz;

DROP TABLE szam_tabla;

6. példa

/* Egy könyv azonosítóját kell megváltoztatni.
Ez nem lehetséges automatikusan, mert
a kolcsonzes tábla konyv oszlopa külső kulcs.
Így azt is meg kell változtatni, hogy
az integritási megszorítás ne sérüljön.
A megszorítás a trigger futása után kerül ellenőrzésre. */
CREATE OR REPLACE TRIGGER tr_konyv_id
  BEFORE UPDATE OF id ON konyv
  FOR EACH ROW
BEGIN
  -- Módosítjuk a kolcsonzes táblát
  UPDATE kolcsonzes SET konyv = :NEW.id
  WHERE konyv = :OLD.id;
END tr_konyv_id;
/
show errors;

UPDATE konyv SET id = 6 WHERE id = 5;

1. példa

/* Az INSTEAD OF triggerek lehetővé teszik nézetek módosítását.
Megpróbáljuk módosítani egy ügyfél nevét az ugyfel_konyv
nézet sorain keresztül. */
UPDATE ugyfel_konyv SET ugyfel = 'József István TRIGGERES'
  WHERE ugyfel_id = 15;

/*
Hiba a(z) 1. sorban:
UPDATE ugyfel_konyv SET ugyfel = 'József István TRIGGERES'
*
ORA-01779: nem módosítható olyan oszlop, amely egy kulcsot nem megőrző táblára utal
*/

/* A következő trigger segítségével egy ügyfél nevét vagy
egy könyv címét módosíthatjuk az ugyfel_konyv nézeten keresztül.
Ha azonosítót is megpróbálnak változtatni,
azzal egyszerűen nem törődünk (nem váltunk ki kivételt).*/
CREATE OR REPLACE TRIGGER tr_ugyfel_konyv_mod
  INSTEAD OF UPDATE ON ugyfel_konyv
  FOR EACH ROW
BEGIN
  IF :NEW.ugyfel <> :OLD.ugyfel THEN
    UPDATE ugyfel SET nev = :NEW.ugyfel
    WHERE id = :OLD.ugyfel_id;
  END IF;
  IF :NEW.konyv <> :OLD.konyv THEN
    UPDATE konyv SET cim = :NEW.konyv
    WHERE id = :OLD.konyv_id;
  END IF;
END tr_ugyfel_konyv_mod;
/
show errors

/* Megpróbálunk módosítani megint. */
UPDATE ugyfel_konyv SET ugyfel = 'József István TRIGGERES'
WHERE ugyfel_id = 15;

/*
5 sor módosítva.
*/

2. példa

/* Az INSTEAD OF triggerek használhatók NESTED TABLE opcióval,
egy nézet kollekcióoszlopának módosítására.
NESTED TABLE előírás csak nézetek esetében használható,
táblák kollekció típusú oszlopaira nem.
Megadunk egy nézetet, amely egy alkérdés eredményét
kollekció típusú oszlopként mutatja. */
CREATE VIEW ugyfel_kolcsonzes AS
  SELECT u.id, u.nev, CAST( MULTISET( SELECT konyv, datum
  FROM kolcsonzes
  WHERE kolcsonzo = u.id) AS T_Konyvek) AS konyvek
  FROM ugyfel u;

/* Töröljük József István 'ECOOP 2001...' kölcsönzését */
DELETE FROM TABLE(SELECT konyvek FROM ugyfel_kolcsonzes WHERE id = 15)
WHERE konyv_id = 25;

/*
Hiba a(z) 1. sorban:
DELETE FROM TABLE(SELECT konyvek FROM ugyfel_kolcsonzes
*
ORA-25015: ezen a beágyazott táblanézet oszlopon nem hajtható végre DML
*/

/* A következő trigger segítségével a visszahozatal adminisztrácója
automatizálható az ugyfel_kolcsonzes tábla konyvek oszlopán keresztül.
Ugyanis egyetlen sor törlése a beágyazott táblából
előidézi a kolcsonzes tábla egy sorának törlését,
az ugyfel tábla konyvek oszlopának módosítását,
valamint a konyv tábla megfelelő bejegyzésének változtatását. */
CREATE OR REPLACE TRIGGER tr_ugyfel_kolcsonzes_del
  INSTEAD OF DELETE ON NESTED TABLE konyvek OF ugyfel_kolcsonzes
  FOR EACH ROW
BEGIN
  DELETE FROM TABLE(SELECT konyvek FROM ugyfel WHERE id = :PARENT.id)
  WHERE konyv_id = :OLD.konyv_id
    AND datum = :OLD.datum;
  DELETE FROM kolcsonzes
  WHERE kolcsonzo = :PARENT.id
    AND konyv = :OLD.konyv_id
    AND datum = :OLD.datum;
  UPDATE konyv SET szabad = szabad + 1
  WHERE id = :OLD.konyv_id;
END tr_ugyfel_kolcsonzes_del;
/
show errors

/* Töröljük József István 'ECOOP 2001...' kölcsönzését */
DELETE FROM TABLE(SELECT konyvek FROM ugyfel_kolcsonzes WHERE id = 15)
WHERE konyv_id = 25;

/*
1 sor törölve.
*/

/* A trigger végrehajtott egy DELETE utasítást a kolcsonzes táblán.
Ellenőrizzük, hogy a korábban létrehozott
tr_kolcsonzes_naploz trigger is lefutott-e? */
SELECT * FROM kolcsonzes_naplo;

/*
KONYV_ISBN         KONYV_CIM     UGYFEL_NEV    UGYFEL_ANYJANEVE  ELVITTE     VISSZAHOZTA MEGJEGYZES
------------------ ------------- ------------- ----------------- ----------- ----------- -------------
ISBN 963 03 9005 1 Java - start! József István Ábrók Katalin     02-ÁPR. -10 06-JÚN. -23
(Megj.: Az eredmény formátumát kisebb méretűvé szabtuk át
az olvashatóság kedvéért.) */

3. példa (Korrelációs nevek használatára)

/* Triggereink olvashatóbbak lehetnek, ha az
alapértelmezett NEW, OLD, illetve PARENT nevekre
azok szemantikájának megfelelő névvel hivatkozunk.
Ezt a REFERENCING előírással adhatjuk meg.
Lássuk egy előző példa módosított változatát: */
CREATE OR REPLACE TRIGGER tr_ugyfel_kolcsonzes_del
  INSTEAD OF DELETE ON NESTED TABLE konyvek OF ugyfel_kolcsonzes
  REFERENCING OLD AS v_Kolcsonzes
  PARENT AS v_Ugyfel
  FOR EACH ROW
BEGIN
  DELETE FROM TABLE(SELECT konyvek FROM ugyfel WHERE id = :v_Ugyfel.id)
  WHERE konyv_id = :v_Kolcsonzes.konyv_id
    AND datum = :v_Kolcsonzes.datum;
  DELETE FROM kolcsonzes
  WHERE kolcsonzo = :v_Ugyfel.id
    AND konyv = :v_Kolcsonzes.konyv_id
    AND datum = :v_Kolcsonzes.datum;
  UPDATE konyv SET szabad = szabad + 1
  WHERE id = :v_Kolcsonzes.konyv_id;
END tr_ugyfel_kolcsonzes_del;
/
show errors

/* Döntse el, melyik forma tetszik jobban, és használja azt következetesen!
A könyv további részeiben maradunk a standard nevek mellett. */

4. példa (Trigger, ahol a törzs egyetlen eljáráshívásból áll)

/* Az előzőek során létrehozott tr_ugyfel_kolcsonzes_del
egy könyv visszahozatalát adminisztrálta.
Ugyanezt a funkciót már megírtuk egy csomagbeli eljárással.
Célszerűbb lenne azt használni, a kód újrahasználása végett. */
CREATE OR REPLACE TRIGGER tr_ugyfel_kolcsonzes_del
  INSTEAD OF DELETE ON NESTED TABLE konyvek OF ugyfel_kolcsonzes
  FOR EACH ROW
  CALL konyvtar_csomag.visszahoz(:PARENT.id, :OLD.konyv_id)
/

Példa

/* Egy csomagváltozóban számláljuk a munkamenetben végrehajtott
sikeres és sikertelen CREATE és DROP utasításokat.
*/
CREATE OR REPLACE PACKAGE ddl_szamlalo IS
  v_Sikeres_create NUMBER := 0;
  v_Sikertelen_create NUMBER := 0;
  v_Sikeres_drop NUMBER := 0;
  v_Sikertelen_drop NUMBER := 0;
  PROCEDURE kiir;
END ddl_szamlalo;
/

CREATE OR REPLACE PACKAGE BODY ddl_szamlalo IS
  PROCEDURE kiir IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD('v_Sikeres_create: ', 25)
      || v_Sikeres_create);
    DBMS_OUTPUT.PUT_LINE(RPAD('v_Sikertelen_create: ', 25)
      || v_Sikertelen_create);
    DBMS_OUTPUT.PUT_LINE(RPAD('v_Sikeres_drop: ', 25)
      || v_Sikeres_drop);
    DBMS_OUTPUT.PUT_LINE(RPAD('v_Sikertelen_drop: ', 25)
      || v_Sikertelen_drop);
  END kiir;
END ddl_szamlalo;
/

CREATE OR REPLACE TRIGGER tr_ddl_szamlalo_bef
  BEFORE CREATE OR DROP
  ON plsql.SCHEMA
BEGIN
  /* Pesszimistán feltételezzük, hogy a
  kiváltó utasítás nem lesz sikeres */
  IF ORA_SYSEVENT = 'CREATE' THEN
    ddl_szamlalo.v_Sikertelen_create :=
      ddl_szamlalo.v_Sikertelen_create + 1;
  ELSE
    ddl_szamlalo.v_Sikertelen_drop :=
      ddl_szamlalo.v_Sikertelen_drop + 1;
  END IF;
END tr_ddl_szamlalo_bef;
/

CREATE OR REPLACE TRIGGER tr_ddl_szamlalo_aft
  AFTER CREATE OR DROP
  ON plsql.SCHEMA
BEGIN
  /* Nem kellett volna pesszimistának lenni:) */
  IF ORA_SYSEVENT = 'CREATE' THEN
    ddl_szamlalo.v_Sikertelen_create :=
      ddl_szamlalo.v_Sikertelen_create - 1;
    ddl_szamlalo.v_Sikeres_create :=
      ddl_szamlalo.v_Sikeres_create + 1;
  ELSE
    ddl_szamlalo.v_Sikertelen_drop :=
      ddl_szamlalo.v_Sikertelen_drop - 1;
    ddl_szamlalo.v_Sikeres_drop :=
      ddl_szamlalo.v_Sikeres_drop + 1;
  END IF;
END tr_ddl_szamlalo_aft;
/

-- Egy sikertelen CREATE
CREATE TRIGGER tr_ddl_szamlalo_aft

/

-- És egy sikeres CREATE

CREATE TABLE abcdefghijklm (a NUMBER)

/

-- És egy sikeres DROP

DROP TABLE abcdefghijklm

/

CALL ddl_szamlalo.kiir();

/*
v_Sikeres_create: 1
v_Sikertelen_create: 1
v_Sikeres_drop: 1
v_Sikertelen_drop: 0
*/

/* Mi lesz az eredmény, ha újra létrehozzuk
a csomag specifikációját?
Magyarázza meg az eredményt! */
CREATE OR REPLACE PACKAGE ddl_szamlalo IS
  v_Sikeres_create NUMBER := 0;
  v_Sikertelen_create NUMBER := 0;
  v_Sikeres_drop NUMBER := 0;
  v_Sikertelen_drop NUMBER := 0;
  -- ez a megjegyzés itt megváltoztatja a csomagot,
  -- ezért tényleg újra létre kell hozni
  PROCEDURE kiir;
END ddl_szamlalo;
/

CALL ddl_szamlalo.kiir();

/*
v_Sikeres_create: 1
v_Sikertelen_create: -1
v_Sikeres_drop: 0
v_Sikertelen_drop: 0
*/

Példa

/* A DBA naplózza a felhasználók be- és kijelentkezéseit
a következő tábla és triggerek segítségével. */
CREATE TABLE felhasznalok_log (
  Felhasznalo VARCHAR2(30),
  Esemeny VARCHAR2(30),
  Hely VARCHAR2(30),
  Idopont TIMESTAMP
);

CREATE OR REPLACE PROCEDURE felhasznalok_log_bejegyez(
p_Esemeny felhasznalok_log.esemeny%TYPE
) IS
BEGIN
  INSERT INTO felhasznalok_log VALUES (
  ORA_LOGIN_USER, p_Esemeny,
  ORA_CLIENT_IP_ADDRESS, SYSTIMESTAMP
);
END felhasznalok_log_bejegyez;
/

CREATE OR REPLACE TRIGGER tr_felhasznalok_log_be
  AFTER LOGON
  ON DATABASE
CALL felhasznalok_log_bejegyez('Bejelentkezés')
/

CREATE OR REPLACE TRIGGER tr_felhasznalok_log_ki
  BEFORE LOGOFF
  ON DATABASE
CALL felhasznalok_log_bejegyez('Kijelentkezés')
/

/* Próbáljon meg be- és kijelentkezni néhány felhasználóval,
ha teheti különböző kliensekről, majd ellenőrizze a tábla tartalmát. */