Triggery v PL/SQL

Trigger (česky spínač, spouštěč) lze definovat jako uloženou proceduru, kterou automaticky volá systém řízení báze dat při definované události. Triggery můžeme rozdělit na tyto základní typy:

systémové triggery
DML triggery - ty nás budou zajímat

DML triggery

DML triggery jsou převážně využívány k udržení konzistence databáze nebo k logování. K udržení integrity databáze je doporučeno používat pouze příslušná integritní omezení, i když to DML triggery také umí. Důvodem je skutečnost, že DML triggery ohlídají aktuálně vkládaná/aktualizovaná/rušená data, ale integritní omezení hlídají všechna data.

Událostí u DML triggerů může být vložení (INSERT), rušení (DELETE) nebo aktualizace (UPDATE) celého nebo části záznamu v tabulce. Triggery bývají obvykle volány:

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

DML triggery mohou být používány i pro databázové pohledy, které nejsou aktualizovatelné. V tomto případě se trigger aktivuje místo definované události - INSTEAD OF.

Definovaný trigger může být aktivován jen jednou nebo pro každý vkládaný/rušený/aktualizovaný záznam opakovaně. Z tohoto důvodu DML triggery dělíme na:

příkazový (statement-level),
řádkový (row-level) - vyžaduje konstrukci FOR EACH ROW

Např. chceme udělat všem zaměstnancům radost, tak jim zvedeneme plat:

UPDATE zamestnanci
   SET plat = 1000000;

Pokud bude na tabulku ZAMESTNANCI navázán příkazový trigger, provede se jen jednou, v případě řádkového triggeru pro každý aktualizovaný záznam tabulky zvlášť.

Jak už bylo řečeno, aktivaci triggerů zajišťuje systém řízení báze dat sám. Také není vyloučeno, že na jednu tabulku může být navázáno více triggerů, které jsou stejného typu (příkazový nebo řádkový), mají stejné načasování a jsou spojeny se stejnou událostí. Pokud nám záleží na pořadí, v jakém mají být aktivovány, musíme do hlavičky triggeru uvést:

FOLLOWS a název triggeru, po kterém má být aktivován
PRECEDES a název triggeru, před kterým má být aktivován

jinak budou aktivovány v libovolném pořadí.

Stejně jako uložená procedura (či funkce), může být trigger v databázi uložen a být neaktivní. V tomto případě musíme do hlavičky triggeru přidat klíčové slovo DISABLE. Trigger je automaticky ukládán v aktivním stavu, proto klíčové slovo ENABLE je nepovinné.

Příkazové triggery

Příkazové triggery, kterým se také někdy říká tabulkové triggery, mají tuto základní syntaxi:

CREATE [OR REPLACE] TRIGGER jmeno
BEFORE | AFTER
INSERT | DELETE | UPDATE [OF sloupec]
ON tabulka
[FOLLOWS | PRECEDES trigger]
[ENABLE | DISABLE]
BEGIN
  PL/SQL blok
END;
/

Př. Chceme logovat jednotlivé změny prováděné v databázi, která obsahuje tabulky PRACOVISTE a ZAMESTNANCI, do tabulky LOGY. Při každé operaci (INSERT, UPDATE, DELETE) s tabulkami PRACOVISTE a ZAMESTNANCI se bude do tabulky LOGY ukládat název tabulky a typ operace (I, U, D) :

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

CREATE TRIGGER tai_pracoviste
AFTER INSERT ON pracoviste
BEGIN
  INSERT INTO logy VALUES ( 'PRACOVISTE', 'I');
END;
/

CREATE TRIGGER tau_pracoviste
AFTER UPDATE ON pracoviste
BEGIN
  INSERT INTO logy VALUES ( 'PRACOVISTE', 'U');
END;
/

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

Pro tabulku ZAMESTNANCI vytvoříme odpovídající triggery analogicky. Z názvu (identifikátoru) triggeru musí být patrné, pro jakou operaci, nad kterou tabulkou a kdy je spouštěn.

Řádkové triggery

Základní syntaxe řádkového triggeru vypadá takto:

CREATE [OR REPLACE] TRIGGER jmeno
BEFORE | AFTER
INSERT | DELETE | UPDATE [OF sloupec]
ON tabulka
[REFERENCING OLD AS stary_zaznam | NEW AS novy_zaznam]
FOR EACH ROW
[FOLLOWS | PRECEDES trigger]
[ENABLE | DISABLE]
[WHEN podminka]
BEGIN
  PL/SQL blok
END;
/

Řádkový trigger nabízí programátorovi přístup na vkládaný/rušený/aktualizovaný záznam prostřednictvím pseudozáznamů NEW a OLD. Pro operaci/událost INSERT není dostupný pseudozáznam OLD, pro operaci/událost DELETE není dostupná pseudozáznam NEW. Pouze operace/událost UPDATE nabízí obě dvě, kde v OLD je původní hodnota a v NEW aktualizovaná hodnota. Řádkový trigger také nabízí konstrukci REFERENCING, kterou je možné pseudozáznamy OLD a NEW si pojmenovat dle svého.

Poslední konstrucí, kterou je řádkový trigger vybaven navíc, je konstrukce WHEN. Touto konstrukcí lze určit podmínku, kdy se tělo triggeru vykoná. V podmínce se obvykle přistupuje k atributům pseudozáznamů OLD a NEW, které jsou obvykle testovány vůči nějaké konstantě. Pokud chceme přistoupit k hodnotě atributu pseudozáznamu v konstrukci WHEN nebo v těle řádkového triggeru, musíme před název pseudozáznamu napsat dvojtečku:

...
WHEN :old.plat < 1000
...

Př. Z předchozího příkladu chceme mít u každého záznamu informaci, kdy byl zadán a jaký uživatel jej zadal. K tomuto účelu rozšíříme tabulky PRACOVISTE a ZAMESTNANCI o sloupce ZADAL a DATUM a vytvoříme odpovídající řádkové triggery:

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

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

Poznámka: v SŘBD Oracle jsou definovány pseudoproměnné USER a SYSDATE, ze kterých lze získat aktuální uživatelské jméno a čas.

DML triggery nad databázovým pohledem

Pokud chceme trigger definovat pro databázový pohled, který není aktualizovatelný, je možné použít načasování INSTEAD OF. Toto načasování říká, že tělo tohoto triggeru se vykoná místo předpokládané události, která je s triggerem spojena. Triggery tohoto typu jsou vždy řádkové, proto konstrukce FOR EACH ROW je zde nepovinná. Pseudozáznamy OLD a NEW jsou v těchto triggerech dostupné pouze pro čtení.

CREATE [OR REPLACE] TRIGGER jmeno
INSTEAD OF
INSERT | DELETE | UPDATE 
ON pohled
[REFERENCING OLD AS stary_zaznam | NEW AS novy_zaznam]
[FOR EACH ROW]
[FOLLOWS | PRECEDES trigger]
[ENABLE | DISABLE]
[WHEN podminka]
BEGIN
  PL/SQL blok
END;
/

Business rules triggery

Řádkové 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.

Nejdříve založíme tabulku OMEZENI a naplníme požadovanými daty ...

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;

... potom vytvoříme tabulku ZAPIS ...

CREATE TABLE zapis (
  os_cislo VARCHAR2(10) NOT NULL,
  predmet  VARCHAR2(10) NOT NULL,
  kredity  NUMBER(2)    NOT NULL
);

a nakonec na tabulku ZAPIS "pověsíme" business trigger :

CREATE OR REPLACE TRIGGER trbi_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;
/

Pokud při vkládání dat do tabulky zapis překročíme maximální povolenou hodnotu, dostaneme následující chybové hlášení :

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

Více událostí v jednom triggeru

Definice hlavičky DML triggeru nemusí být závislá jen na jedné události, je možné jich kombinovat více. Definice kombinací více událostí v jednom triggeru může vypadat např. takto:

...
INSERT OR UPDATE OF sloupec OR DELETE
...

V těle triggeru potřebujeme zjistit, jaká událost jej aktivovala. K tomuto účelu slouží tzv. podmínkové predikáty, které vrací hodnotu typu BOOLEAN.

INSERTING - trigger aktivovala operace INSERT
UPDATING - trigger aktivovala operace UPDATE
UPDATING (sloupec) - trigger aktivovala operace UPDATE nad sloupcem sloupec
DELETING - trigger aktivovala operace DELETE

Pořadí aktivace DML triggerů

Pokud je na tabulku navázáno více trigerů různých typů a různého načasování, jsou tyto triggery aktivovány v tomto pořadí:

BEFORE příkazové triggery,
BEFORE řádkové triggery
provede se samotná událost
AFTER řádkové triggery
AFTER příkazové trigery

Jestliže je na tabulku navázáno více triggerů se stejným bodem načasování (timing point), a je důležité pořadí aktivace těchto triggerů, je nezbytné použítí konstrukcí FOLLOWS nebo PRECEDES, jinak jsou tyto triggery aktivovány v libovolném pořadí.

Aktivace a deaktivace DML triggerů

Existují případy, kdy potřebujeme dočasně deaktivovat všechny triggery navázané na danou tabulku. Např. při exportu a následném importu stejných dat do nové databáze. V těchto případech je zbytečné, aby triggery byly aktivní, protože zbytečně zvyšují zátěž databázového serveru při importu dat. Potom je vhodné jedním příkazem deaktivovat a následně po importu dat aktivovat všechny DML triggery navázané na danou tabulku:

ALTER TABLE tabulka DISABLE ALL TRIGGERS;

ALTER TABLE tabulka ENABLE ALL TRIGGERS;

Systémové triggery

Systémové triggery jsou vázány:

na schéma (uživatele) databáze. Spouštěcí událostí může být DDL příkaz (např. založení tabulky). Proto se jim také říká DDL triggery.

CREATE [OR REPLACE] TRIGGER jmeno
BEFORE | AFTER | INSTEAD OF
CREATE | DROP | ALTER | TRUNCATE | ...
ON login.SCHEMA
[FOLLOWS | PRECEDES trigger]
[ENABLE | DISABLE]
BEGIN
  PL/SQL blok
END;
/

na celou databázi. Spouštěcí událostí může být databázová operace, např. start databáze.

CREATE [OR REPLACE] TRIGGER jmeno
BEFORE SHUTDOWN | BEFORE LOGOFF |
AFTER STARTUP | AFTER LOGON | AFTER SERVERERROR | ...
ON DATABASE
[FOLLOWS | PRECEDES trigger]
[ENABLE | DISABLE]
BEGIN
  PL/SQL blok
END;
/

Aktivace, deaktivace a zrušení triggeru

Jakýkoliv trigger je možno deaktivovat (zakázat):

ALTER TRIGGER trigger DISABLE;

aktivovat (povolit):

ALTER TRIGGER trigger ENABLE;

a zrušit (smazat):

DROP TRIGGER trigger;

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. Syntaxe 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^28 - 1 nebo -1*/
   [MINVALUE     <celé číslo> ] /* default 1 nebo -10^28 + 1*/
   [CYCLE | NOCYCLE]            /* default NOCYCLE */

Význam jednotlivých klauzulí je následující :

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

Každá sekvence obsahuje dva pseudosloupce, CURRVAL a NEXTVAL. Pseudosloupec CURRVAL poskytuje aktuální hodnotu čítače sekvence, NEXTVAL aktuální hodnotu čítače zvedne o požadovaný přírůstek a tuto hodnotu poskytuje. Vše je demonstrováno na následujících příkladech :

CREATE SEQUENCE test_seq MAXVALUE 10 NOCYCLE NOCACHE;

SELECT test_seq.NEXTVAL FROM DUAL;
SELECT test_seq.NEXTVAL FROM DUAL;

SELECT test_seq.CURRVAL FROM DUAL;

SELECT test_seq.NEXTVAL FROM DUAL;

V případě, že je sekvence nadefinována jako NOCYCLE a je překročena hodnota MAXVALUE, je vypsána chybová hláška :

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

V tom případě nám pomůže jeden z následujících příkazů :

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

Automatické číslování

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 trbi_pracoviste
BEFORE INSERT 
    ON pracoviste
   FOR EACH ROW
BEGIN
   SELECT seq_id_prac.NEXTVAL INTO :new.id_prac FROM DUAL;
END;
/

Nyní můžeme otestovat funkčnost automatického číslování :

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

Automatické číslování - Oracle 12c a vyšší

SŘBD Oracle od prvního vydání verze 12c rozšířil příkaz CREATE TABLE o podporu automatického číslování pro celočíselný sloupec, který plní funkci primárního klíče tabulky.

Výše uvedená tabulka PRACOVISTE by byla definována takto:

CREATE TABLE pracoviste (
  id_prac  NUMBER(5)    GENERATED AS IDENTITY (MAXVALUE 99999) PRIMARY KEY,
  nazev    VARCHAR2(30) NOT NULL
);

V databázi se ve skutečnosti založí dva propojené objekty - tabulka a sekvence, která je použita pro získání další hodnoty (pseudosloupec NEXTVAL) jako výchozí hodnota pro sloupec id_prac:

CREATE SEQUENCE iseq$$_xxxxxx MAXVALUE 99999;

CREATE TABLE pracoviste (
  id_prac  NUMBER(5)    DEFAULT iseq$$_xxxxxx.NEXTVAL PRIMARY KEY,
  nazev    VARCHAR2(30) NOT NULL
);

Takto systémem vytvořenou sekvenci nelze zrušit příkazem DROP SEQUENCE, ale bude automaticky zrušena příkazem zrušení tabulky PRACOVISTE.

Kontrolní úloha

Vytvořte tabulky CV_DRUH_ZBOZI a CV_ZBOZI, které mají následující strukturu:

CV_DRUH_ZBOZI:

id_druh_zbozi (celé číslo, primární klíč)
druh (povinná textová položka max. délky 50 znaků)
min_cena (povinná číselná položka)
max_cena (povinná číselná položka)

CV_ZBOZI:

id_zbozi (celé číslo, primární klíč)
popis (textová položka max. délky 50 znaků)
id_druh_zbozi (číselná položka, cizí klíč na tabulku CV_DRUH_ZBOZI)
kupni_cena (povinná číselná položka)

Vytvořte trigger, který v tabulce ZBOZI:

zajistí automatické číslování sloupce id_zbozi (použijte sekvenci),
kontroluje, zda cena zadávaného zboží je v intervalu (min_cena, max_cena) s ohledem na druh vkládaného zboží. Pokud cena neodpovídá, dané zboží nebude vloženo.