Lections

10. Triggers

Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров.Триггер - это выполняемый модуль, привязанный к объекту базы данных и событию, связанному с этим объектом. Триггер вызывается неявно при возникновении события над этим объектом. Триггеры имею следующие характеристики -

• Тип триггера - 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;