9. CVIČENÍ

Triggery

Trigger lze definovat příkazem CREATE TRIGGER, který má následující syntax :

CREATE TRIGGER jméno triggeru
BEFORE | AFTER
DELETE | INSERT | < UPDATE [OF < jméno sloupce >]>
ON jméno tabulky
[REFERENCING < OLD AS < jméno > | NEW AS < jméno >  >]
[FOR EACH ROW]
[WHEN < podmínka >]
BEGIN
 < ... PL/SQL blok ... > 
END;

• před specifikovanou událostí - BEFORE nebo
• po specifikované události - AFTER

INSERT INTO tab1 SELECT * FROM tab2;

Trigger je pak buď :

• tabulkový nebo
• řádkový - FOR EACH ROW

Vlastní obsluhu události lze nadefinovat v PL/SQL bloku. Uvnitř PL/SQL bloku (a také v klauzuli WHEN) se lze odkazovat na původní a nový záznam pomocí pseudoproměnných :new a :old, kde :new obsahuje vkládaný záznam a :old původní záznam (tedy např. :new.plat < 2000). Je zřejmé, že při vkládání nového záznamu není definována pseudoproměnná :old a analogicky při mazání :new. Stejně tak nejsou tyto pseudoproměnné definovány u tabulkových triggerů. Jejich jména lze předefinovat v klauzuli REFERENCING OLD AS, popř. REFERENCING NEW AS.

Příklad : Chceme logovat jednotlivé změny prováděné v databázi, která obsahuje tabulky Pracoviště a Zaměstnanci, do tabulky Logy. Do této tabulky bude při každé operaci nad danými tabulkami uloženo jméno tabulky a typ operace :

CREATE TABLE logy (
  tab  VARCHAR2(20),
  op   CHAR(1)
);

CREATE TRIGGER tia_log_prac
AFTER INSERT ON pracoviste
BEGIN
  INSERT INTO logy VALUES ( 'PRACOVISTE', 'I');
END;
/
CREATE TRIGGER tda_log_prac
AFTER DELETE ON pracoviste
BEGIN
  INSERT INTO logy VALUES ( 'PRACOVISTE', 'D');
END;
/

Příklad : U každého záznamu chceme mít informaci kdy byl zadán a jaký uživatel jej zadal. K tomuto účelu rozšíříme tabulky o sloupce zadal a datum a vytvoříme řádkový trigger :

CREATE TRIGGER tib_log_zam
BEFORE INSERT ON zamestnanec
FOR EACH ROW
BEGIN
  :new.zadal := user;
  :new.datum := sysdate;
END;
/

Příklad : U každého záznamu veďte navíc evidenci o uživateli, který jej změnil. Vytvořte tabulku obnova, do které se budou automaticky uchovávat všechny smazané záznamy a napište příkaz, kterým lze smazané záznamy znovu obnovit. Obnovené záznamy ze záložní tabulky vymažte.

Malá případová studie

Triggery jsou také často používány při realizaci tzv. "business rules", tj. integritních omezení specifických pro danou oblast použití. Následující příklad ukazuje část pomyslné studijní agendy, ve které si mohou studenti zapsat maximálně 20 kreditů za semestr. Protože tento požadavek se může měnit, je zavedena tabulka OMEZENI, ve které jsou uloženy všechny potřebné hraniční hodnoty.

create table zapis (
    os_cislo varchar2(10) not null,
    predmet  varchar2(10) not null,
    kredity  number(2)    not null
);

create table omezeni (
    typ          varchar2(30) not null,
    min_hodnota  number(5)    not null,
    max_hodnota  number(5)    not null
);

insert into omezeni values ('KRED_SEMESTR', 10, 20);
insert into omezeni values ('KRED_STUDIUM', 300, 320);

commit;

create or replace trigger tib_zapis
    before insert
    on zapis
    for each row

declare
  suma     integer;
  maximum  integer;
  errno    integer;
  errmsg   varchar2(256);

begin
  select  sum(kredity) into suma
    from  zapis
   where  os_cislo = :new.os_cislo;

  select  max_hodnota into maximum
    from  omezeni
   where  typ = 'KRED_SEMESTR';

  if ((suma + :new.kredity) > maximum) then
    errno  := -20001;
    errmsg := 'Prekroceno maximum '||to_char(maximum)||' kreditu za semestr !';
    raise_application_error(errno, errmsg);
  end if;
end;
/

ORA-20001: Prekroceno maximum 20 kreditu za semestr !
ORA-06512: at "MAX.TIB_ZAPIS", line 19
ORA-04088: error during execution of trigger 'MAX.TIB_ZAPIS'

ALTER TRIGGER jméno ENABLE;

ALTER TRIGGER jméno DISABLE;

DROP TRIGGER jméno;

• Další užitečné informace o triggerech

Sekvence

Často potřebujeme automaticky generovat hodnotu primárního klíče (identifikátor objektu, číslo pracoviště nebo zaměstnance). K tomuto účelu jsou určeny tzv. sekvence, které si lze představit jako sdílené globální čítače. Souběžný přístup k sekvenci je řešen automaticky. Syntax příkazu pro vytvoření sekvence je následující :

CREATE SEQUENCE <jméno>
   [INCREMENT BY] <celé číslo>   /* default 1 */
   [START WITH]   <celé číslo>   /* default 1 */
   [MAXVALUE]     <celé číslo>   /* default 10^27 */
   [MINVALUE]     <celé číslo>   /* default */
   [CYCLE | NOCYCLE]             /* default NOCYCLE */

• INCREMENT BY - hodnota o kterou má být čítač zvětšen (může být i záporná)
• START WITH - hodnota od které má čítač začít (hodí se chceme-li sekvenci použít pro generování klíče do tabulky, ve které jsou již data)
• MAXVALUE - nejvyšší povolená hodnota čítače
• NOCYCLE - při překročení MAXVALUE nastane chyba
• CYCLE - při překročení MAXVALUE začne čítač znova od MINVALUE

CREATE SEQUENCE test_seq MAXVALUE 10 NOCYCLE;

SELECT test_seq.nextval FROM dual;
SELECT test_seq.nextval FROM dual;

SELECT test_seq.currval FROM dual;

SELECT test_seq.nextval FROM dual;

ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

ALTER SEQUENCE test_seq CYCLE;
ALTER SEQUENCE test_seq MAXVALUE 1000;
ALTER SEQUENCE test_seq NOMAXVALUE;

Příklad použití sekvence a triggeru :

Pomocí sekvence je automaticky generován identifikátor pracoviště (primární klíč) a vložen do záznamu řádkovým triggerem :

CREATE TABLE pracoviste (
  id_prac  NUMBER(5)    PRIMARY KEY,
  nazev    VARCHAR2(30) NOT NULL
);

CREATE SEQUENCE seq_id_prac MAXVALUE 99999;

CREATE TRIGGER tib_prac
  BEFORE INSERT ON pracoviste
  FOR EACH ROW
BEGIN
   SELECT seq_id_prac.nextval INTO :new.id_prac FROM dual;
END;
/

INSERT INTO pracoviste (nazev) VALUES ('KIV');
INSERT INTO pracoviste VALUES (NULL, 'CIV');