Инспектор
Вы можете этот курс.
Опубликован: 11.03.2009 | Уровень: специалист | Доступ: платный
Лекция 11:
Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров
Аннотация: Лекция посвящена теме триггеров. В теоретической части даются основные понятии, рассказывается о типах триггеров и общей схеме их активизации. А также, рассматриваются практические задания по теме.
Ключевые слова: триггер, модуль, объект базы данных, D-триггер, DDL, DML, viewing, системный объект, insertion, UPDATE, deletion, системное событие, оператор, SQL, Oracle, sql server, удаление триггера, DROP, triggering, table, порядок выполнения триггеров
Триггер - это выполняемый модуль, привязанный к объекту базы данных и событию, связанному с этим объектом. Триггер вызывается неявно при возникновении события над этим объектом. Триггеры имеют следующие характеристики -
- Тип триггера - 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;