НОУ ИНТУИТ | Распределенные базы и хранилища данных. Лекция 11: Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров

Опубликован: 11.03.2009 | Доступ: свободный | Студентов: 2843 / 723 | Оценка: 4.22 / 4.22 | Длительность: 11:41:00

Тема: Базы данных

Аннотация: Лекция посвящена теме триггеров. В теоретической части даются основные понятии, рассказывается о типах триггеров и общей схеме их активизации. А также, рассматриваются практические задания по теме.

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

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

Синтаксис команды создания триггера create trigger

create trigger [владелец.]название_триггера
  on [владелец.]название_таблицы
  for {insert, update, delete}
  as SQL_операторы

create 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;

Дальше >>

Авторизоваться

Распределенные базы и хранилища данных

Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров