Триггеры в Oracle

Триггеры DML

CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE | UPDATE OF список_столбцов} ON имя_таблицы
[FOR EACH ROW]
[WHEN (...)]
[DECLARE ...]
BEGIN

... исполняемые операторы ...

[EXCEPTION ...]
END [имя_триггера];



Триггер это именованный блок PL/SQL.
Он не может быть вызван из процедуры и не принимает никаких параметров при вызове.
Этот блок срабатывает при определенном событии а именно при запуске операций DML:
-INSERT
-UPDATE
-DELETE

Существуют еще и системные триггеры, которые срабатывают на события самой БД.

Триггеры используют:
Для реализации сложных ограничений целостности данных, которые невозможно осуществить
через описательные ограничения, установленные при создании таблиц.
Организации всевозможных видов аудита.
Оповещения других модулей о том, что делать в случае изменения информации содержащейся в БД.
Для реализации так называемых "бизнес-правил"
Для организации каскадных воздействий на таблицы БД.

Бывают триггеры операторные и строковые.
Операторный триггер активизируется один раз до или после оператора DML/
Строковый триггер активизируется один раз для каждой строки, на которую воздействует оператор DML.

Изменил оператор UPDATE две строки в таблице: строковый триггер активизируется два раза,
а операторный один.

Пример операторного триггера:

create or replace trigger bfotst
    before update on tab1
declare
begin
    insert into tab2(col1, col2, col3, col4)
        values(USER, SYSDATE, 'Update', 'Before statement trigger');
end bfotst;
/



create or replace trigger afttst
    after update on tab1
declare
begin
    insert into tab2(col1, col2, col3, col4)
        values(USER, SYSDATE, 'Update', 'After statement trigger');
end afttst;
/


Строковые триггеры:

create or replace trigger bfotstr
    before update on tab1
    for each row
declare
begin
    insert into tab2(col1, col2, col3, col4)
        values(USER, SYSDATE, 'Update', 'Before row trigger');
end bfotstr;
/


create or replace trigger afttstr
    after update on tab1
    for each row
declare
begin
    insert into tab2(col1, col2, col3, col4)
        values(USER, SYSDATE, 'Update', 'After row trigger');
end afttstr;
/


before - триггер активируется до срабатывания оператора DML.
after  - треггер активируется после срабатывания оператора DML.


Строковый триггер срабатывает один раз для каждой строки.
При этом внутри триггера можно обращаться к строке обрабатываемой
в данный момент времени, с помощью двух псевдозаписей:

:old
:new


create or replace trigger dlttstr
    before delete on tab1
    for each row
declare
begin

    a =  :old.col1;
    c =  :new.col2;
    b =  :old.col3;
 .
 .
 .
end dlttstr;
/

                           :OLD                                            :NEW
--------------------------------------------------------------------------------
INSERT :    NULL                                              значение после вставки
UPDATE :  значение перед изменением       значение после изменения
DELETE :  значение перед удалением          NULL

Рассмотрим пример:

Имеется таблица tab1 с первичным ключом:

ID
col1
col2
col3

Пытаемся в нее сделать вставку так:

insert into tab1(col1, col2, col3) values(var1, var2, var3);

получаем ошибку: невозможно вставить NULL  в ID

Делать нужно так:

создаем последовательность:

create sequence seq1
 start with 8000
 increment by 1;
/

создаем триггер:

create or replace trigger insIDtrig
    before insert on tab1
    for each row
declare
begin
    select seq1.nextval into  :new.ID from dual;
end insIDtrig;
/

теперь можно делать наш INSERT.


Еще пример:

Имеются две таблицы:

tab1:

ID
col1
col2

tab2:

ID
col
col2
idt1

Хотим чтобы вставляя строки в главную таблицу, аналогично вставлялись строки
и в подчиненную таблицу.

Для главной таблицы мы уже создали sequence seq1 и триггер insIDtrig,
который добавляет значения в поле ID таблицы tab1.

Создадим для подчиненной таблицы sequence

create sequence seq2
 start with 5
 increment by 1;
/

и создадим триггер:

create or replace trigger aftinsttrig
    after insert on tab1
    for each row
declare
begin
    insert into tab2(tab2.ID, tab2.col1, tab2.col2, tab2.idt1)
    values(seq2.nextval, :new.col1, :new.col2, :new.ID);
end aftinsttrig;
/


Напишем аналогичный триггер на UPDATE:

create or replace trigger aftupdttrig
    after update on tab1
    for each row
declare
begin
    update tab2
    set
         tab2.col1 =  :new.col1,
         tab2.col2 =  :new.col2,
         tab2.idt1 =  :new.ID
    where
         tab2.idt1 =  :old.ID;
end aftupdttrig;
/


И на удаление:
(каскадное удаление)

create or replace trigger bfrdelttrig
    before delete on tab1
    for each row
declare
begin
    delete from tab2
    where tab2.idt1 =  :old.ID;
end bfrdelttrig;
/

Менять псевдозапись :new в строковом триггере AFTER не имеет смысла,
так как событие уже обработано.

Менять псевдозапись :new возможно в строковом триггере before.
А псевдозапись :old никогда не модифицируется, а только считывается.


Предложение WHEN

Используйте предложение WHEN для уточнения условий выполнения кода триггера.

В этом примере код триггера будет исполняться только при изменении col1 в таблице tab1:

create or replace trigger check_col1_trg
    after update of col1 on tab1
    for each row
when (:old.col1 != :new.col1) or
     (:old.col1 is null and :new.col1 is not null) or
     (:old.col1 is not null and :new.col1 is null)
begin
   .....    

end check_col1_trg;
/



Ограничить время исполнения триггера insert периодом с 9 утра до 5 часов вечера можно так:

create or replace trigger check_time_trg
    before insert on tab1
    for each row
when ( to_char(SYSDATE, 'HH24') between 9 and 17 )

begin
   .....    

end check_time_trg;
/


Триггеры DDL

CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER} {DDL-событие}
ON {SCHEMA | DATABASE}
[WHEN (...)]
DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END имя_триггера;
/


DDL - события:

BEFORE / AFTER ALTER
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND


Пример:

CREATE TABLE tab1(
col1 VARCHAR2(20)NOT NULL,
col2 VARCHAR2(20)NOT NULL,
col3 DATE NOT NULL,
col4 VARCHAR2(20)NOT NULL,
col5 DATE,
col6 VARCHAR2(20)
);


CREATE OR REPLACE TRIGGER after_ddl_creation
AFTER CREATE ON SCHEMA
BEGIN
  INSERT INTO tab1 VALUES
  (SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_TYPE,SYSDATE,USER,NULL,NULL);
END;
/


Триггеры событий базы данных

CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER} {событие базы данных}
ON {SCHEMA | DATABASE}
DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END имя_триггера;
/

События базы данных:

AFTER STARTUP
BEFORE SHUTDOWN

AFTER LOGON
BEFORE LOGOFF

AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover
AFTER SUSPEND
AFTER SERVERERROR (does not trap ...

    ORA-01403: no data found (this is in the Oracle docs but does not seem to be correct)
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-01034: ORACLE not available
    ORA-04030: out of process memory when trying to allocate string bytes (string, string)


Пример:

CREATE TABLE tab1 (
col1 DATE,
col2 VARCHAR2(20));

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
  INSERT INTO tab1
  (col1, col2)
  VALUES
  (SYSDATE, USER);
END proc1;
/

CREATE OR REPLACE TRIGGER logintrig
AFTER LOGON ON DATABASE
CALL proc1
/


Получить информацию о триггере:

select * from user_triggers;

Активировать триггер:

alter trigger имя_триггера enable;

Активировать все триггеры для конкретной таблицы:

alter table имя_таблицы enable all triggers;

Заблокировать триггер:

alter trigger имя_триггера disable;

Удалить триггер:

drop trigger имя_триггера;