Триггеры 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 имя_триггера;
