Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров.Триггер - это выполняемый модуль, привязанный к объекту базы данных и событию, связанному с этим объектом. Триггер вызывается неявно при возникновении события над этим объектом. Триггеры имею следующие характеристики -
- Тип триггера - DDL или DML
- Объект - таблица, VIEW, системный объект для DDL триггеров
- Событие - insert, update, delete для таблицы и DML, instead of для VIEW или системное событие для DDL триггеров.
- Способ активации - для всего оператора или для каждой строки
- Время активации - до или после выполнения оператора.
Триггеры в T-SQL по функциональности беднее триггеров в Oracle. В SQL Server существуют только after или instead of триггеры, вызываемые для всего оператора.
Триггеры SQL Server
Создание триггеров
create trigger trg on my_table for insert, update, delete as select "this is trigger"
Синтаксис команды создания триггера creat trigger
creat trigger [владелец.]название_триггера
on [владелец.]название_таблицы
for {insert, update, delete}
as SQL_операторы
creat trigger [владелец.]название_триггера
on [владелец.]название_таблицы
for {insert, update}
as
[if update (название_столбца) [{and | or} update (название_столбца)] ... ]
SQL_операторы
[if update (название_столбца) [{and | or} update (название_столбца)] ...
SQL_операторы] ...
CREATE trigger dbo.TD_Inscribe on Inscribe
for DELETE
as
begin
delete PP_S_ImageObject
where PP_S_ImageObject.ImageObjectID in (select deleted.PhotoID from deleted)
delete from
ImageObject
where
ImageObject.ImageObjectID in (select deleted.PhotoID from deleted)
end
Удаление триггеров
Команда удаления триггера drop trigger имеет следующий вид:
drop trigger [владелец.]название_триггера
[, [владелец.]название_триггера] ...
Таблицы INSERTED и DELETED
При вызове триггеров используются две специальные таблицы: таблица удаления (deleted table) и таблица добавления (inserted table). Они используются для проверки операторов модификации данных и создания условий для работы триггеров. Пользователь не может непосредственно изменять данные в этих таблицах, но может использовать находящуюся в них информацию для проверки последствий выполнения операторов insert, update или delete.
В таблице deleted сохраняются копии строк, которые удаляются операторами update или delete. В процессе выполнения этих операторов строки удаляются из триггерной таблицы и помещаются в таблицу удаления. Обычно триггерная таблица и таблица удаления не имеют общих строк.
В таблице inserted сохраняются копии строк, которые вставляются операторами insert или update. В процессе выполнения этих операторов новые строки вставляются в таблицу добавления и триггерную таблицу одновременно. Таким образом, таблица добавления всегда содержит копии новых строк, которые были добавлены в триггерную таблицу.
CREATE trigger dbo.TU_Inscribe on Inscribe
for UPDATE
as
begin
if exists (
select
1
from
deleted, inserted
where
deleted.IdentifyDocumentID = inserted.IdentifyDocumentID and deleted.InscribeID = inserted.InscribeID and
deleted.PhotoID is not null and inserted.PhotoID is null
)
begin
delete PP_S_ImageObject
where
PP_S_ImageObject.ImageObjectID in
(select
deleted.PhotoID
from
deleted, inserted
where
deleted.IdentifyDocumentID = inserted.IdentifyDocumentID and deleted.InscribeID = inserted.InscribeID and
deleted.PhotoID is not null and inserted.PhotoID is null
)
delete
ImageObject
from
ImageObject
where
ImageObject.ImageObjectID in
(select
deleted.PhotoID
from
deleted, inserted
where
deleted.IdentifyDocumentID = inserted.IdentifyDocumentID and deleted.InscribeID = inserted.InscribeID and
deleted.PhotoID is not null and inserted.PhotoID is null
)
end
end
Триггеры Oracle
Подготовка примера
drop table test;
create table test(i integer primary key, s varchar2(100));
begin
delete from test;
insert into test values(1, 'aaa');
insert into test values(2, 'bbb');
commit;
end;
create sequence test_s start with 1;
Создание триггера
drop trigger t;
create or replace trigger t_i
before insert /* or update or delete */
-- after insert /* or update or delete */
on test
for each row
when (new.i is null)
begin
dbms_output.put_line('trigger ok');
select test_s.nextval into :new.i from dual;
end;
create or replace trigger t_i
before insert /* or update or delete */
-- after insert /* or update or delete */
on test
referencing new as NN old as OO
for each row
when (nn.i is null)
begin
dbms_output.put_line('trigger ok');
select test_s.nextval into :nn.i from dual;
end;
Типы триггеров и порядок их выполнения
Пример показывает порядок выполнения триггеров всех типов
drop table t;
create table t(i integer);
create or replace trigger tb_d
before delete on t
begin
dbms_output.put_line('tb_d');
end;
create or replace trigger tbr_d
before delete on t
for each row
begin
dbms_output.put_line('tbr_d');
end;
create or replace trigger ta_d
after delete on t
begin
dbms_output.put_line('ta_d');
end;
create or replace trigger tar_d
after delete on t
for each row
begin
dbms_output.put_line('tar_d');
end;
begin
insert into t values(1);
insert into t values(2);
end;
Использование предикатов inserting, ... и new, old
drop table test_log;
create table test_log(op_user varchar2(100), op_date date, i integer, s varchar2(100));
create or replace trigger t_ud
before update of i, s or delete
on test
for each row
begin
if updating('s') then
if :new.s > :old.s then
raise_application_error(-20000, 'update error');
end if;
elsif deleting then
insert into test_log values(user, sysdate, :old.i, :old.s);
end if;
end;
Выполнение триггреров в той же транзакции
delete from test; select * from test_log; rollback; select * from test_log;
Активизация и деактивизация триггеров
alter trigger t_ud disable; alter table test enable all triggers;
Получение информации о триггерах
select * from user_triggers; select * from user_objects;
Запрет изменения new, old в after ... и table level триггерах
create or replace trigger ta_ud
after update
on test
for each row
begin
:new.s := 'aaaaaaa';
end;
create or replace trigger ta_ud
before update
on test
for each row
begin
:old.s := 'aaaaaaa';
end;
create or replace trigger tb_ud
before update
on test
begin
:new.s := 'aaaaaaa';
end;
Несколько триггеров одного типа
create or replace trigger ta_ud1
after update
on test
for each row
begin
dbms_output.put_line('trigger update 1');
end;
create or replace trigger ta_ud2
after update
on test
for each row
begin
dbms_output.put_line('trigger update 2');
end;
Использование исключений
create or replace package p_exc
as
invalid_str exception;
end;
create or replace trigger t_ui
before insert or update
on test
for each row
begin
if :new.s < 'c' then
raise p_exc.invalid_str;
end if;
end;
begin
delete from test;
commit;
insert into test values(1, 'aaa');
exception
when p_exc.invalid_str then
dbms_output.put_line('invalid_str: ' || sqlcode || ' ' || sqlerrm);
end;
Триггеры instead of
create table tp(i integer primary key);
create table tc(k integer primary key, i integer references tp(i));
begin
commit;
delete from tc;
delete from tp;
commit;
insert into tp values(1);
insert into tp values(2);
insert into tc values(5, 1);
insert into tc values(6, 1);
insert into tc values(7, 2);
commit;
end;
create or replace view v as select tp.i ii, tc.k kk from tp inner join tc on tp.i = tc.i;
select * from user_updatable_columns where table_name = 'V';
create or replace trigger vt
instead of insert
on v
for each row
declare
ii integer;
begin
begin
select i into ii from tp where i = :new.ii;
exception
when no_data_found then
insert into tp(i) values(:new.ii);
end;
update tc set i = :new.ii where k = :new.kk;
if sql%rowcount = 0 then
insert into tc(k, i) values(:new.kk, :new.ii);
end if;
end;
Изменяющиеся таблицы
create table grp(gn number primary key, gname varchar2(10)); create table std(sn number primary key, gn number references grp, sname varchar2(100));
При добавлении группы студента в группу с максимальным кол-вом студентов, перенаправляем его в группу с минимальным кол-вом студентов
create or replace trigger std_i_a
after insert
on std
for each row
declare
n number;
begin
dbms_output.put_line('OK insert after');
select count(*) into n from std where gn = :new.gn;
dbms_output.put_line('n = ' || n);
end;
create or replace trigger std_i_b
before insert
on std
for each row
declare
n number;
begin
dbms_output.put_line('OK insert before');
select count(*) into n from std where gn = :new.gn;
dbms_output.put_line('n = ' || n);
end;
create or replace trigger std_i_a_all
after insert
on std
declare
n number;
begin
dbms_output.put_line('OK insert after all');
select count(*) into n from std;
dbms_output.put_line('n = ' || n);
end;
create or replace trigger std_i_b_all
before insert
on std
declare
n number;
begin
dbms_output.put_line('OK insert before all');
select count(*) into n from std;
dbms_output.put_line('n = ' || n);
end;