5.7.4. Триггеры

Идея триггеров

Иногда существуют какие-то действия, которые надо выполнять всегда при модификации той или иной таблицы. Например, есть таблица с важными данными и любое изменение надо фиксировать - заносить в протокол кто, что и когда модифицировал в данной таблице. Или, например, поддержание целостности базы данных - при удалении из базы данных информации о некоторой фирме, надо удалить и информацию о работающих в ней людях.

Для подобных задач в SQL введено понятие триггера, Триггер - это механизм, который автоматически выполняет некоторый набор SQL-операторов когда происходит некоторое событие. То есть триггер задается парой "событие-действие". Событиями, на которые можно установить триггер, являются модификации данных, то есть операторы DELETE, INSERT, UPDATE. Причем, триггер связан с конкретной таблицей. То есть событием, вызывающим триггер (триггерным событием) является выполнение операторов удаления, вставки или модификации конкретной таблицы.

Триггер хранится как объект в базе данных, то есть принадлежит схеме базы данных. ¹²)

Создание и удаление триггера

Для создания и удаления триггеров, как и для таблиц, и для хранимых процедур, используются операторы CREATE и DROP.

CREATE TRIGGER <имя> <событие> <действие> DROP TRIGGER <имя>

Имя триггера - обычное имя в SQL. Главное, что бы оно было уникальным. Событие указывает момент, когда триггер срабатывает. Триггеры бывают трех типов - на вставку нового ряда в таблицу, удаление ряда и модификацию каких-либо полей. Для каждой таблицы могут существовать все три типа триггеров, как, впрочем, и не существовать ни одного. При этом, если для каждой таблицы может существовать только по одному триггеру на вставку и удаление, то триггеров на обновление можжет быть несколько в зависимости от того, какие поля обновляются (но не может быть двух триггеров на обновление одного и того же поля).

Соответственно, синтаксис на раздел "событие" при создании триггера будет следующим:

INSERT ON <имя таблицы> DELETE ON <имя таблицы> UPDATE ON <имя таблицы> UPDATE OF <поле>, <поле>, ... ON <имя таблицы>

Событием для триггера является сам факт выполнения оператора вставки, удаления или модификации. То есть, если даже оператор удаления не удалил ни одной записи (ни одна запись не удовлетворила условию), все равно триггер на удаление сработает. Примеры операторов создания триггеров, пока без написания того, что они будут исполнять:

{ триггер на вставку нового документа } CREATE TRIGGER trig1 INSERT ON documents ....

{ триггер на удаление информации о фирме } CREATE TRIGGER del_trig DELETE ON companies ....

{ триггер на обновление цены товара } CREATE TRIGGER upd_price UPDATE OF price ON items ....

{ триггер на изменение имени или фамилии человека } CREATE TRIGGER upd_name UPDATE OF lname, fname ON persons ....

Теперь рассмотрим, как описываются действия триггера. Каждое действие состоит из описания того, что выполняется единожды перед началом исполнения оператора, вызвавшего событие для триггера (раздел BEFORE), для каждого ряда (раздел FOR EACH ROW) и после исполнения оператора (раздел AFTER).

CREATE TRIGGER .... ON ... BEFORE <операторы> FOR EACH ROW <операторы> AFTER <операторы>

Можно использовать произвольное сочетание из разделов BEFORE, FOR EACH ROW и AFTER, главное, что бы был хотя бы один раздел. В качестве операторов для триггеров могут быть использованы всего четыре типа SQL-операторов - оператор вставки (INSERT), удаления (DELETE), обновления (UPDATE) и выполнения хранимой процедуры (EXECUTE PROCEDURE). Очевидно, что наличие в этом списке оператора вызова хранимой процедуры позводяет сделать триггер сколь угодно сложным. Эти SQL-операторы должны разделяться запятой и находиться в круглых скобках.

Примеры:

CREATE TRIGGER trig1 DELETE ON persons AFTER ( UPDATE tab2 SET x=x+1, UPDATE tab3 SET y=y-1 )

CREATE TRIGGER upd_trig UPDATE OF name ON companies FOR EACH ROW ( EXECUTE PROCEDURE proc1 ) AFTER ( EXECUTE PROCEDURE proc2 )

Рассмотрим поведение последнего триггера (upd_trig) более подробно. Пусть таблица companies имеет следующую структуру и состоит из следующих записей:

CREATE TABLE companies ( company_id SERIAL { уникальный идентификатор фирмы } name CHAR(40) { название фирмы } address CHAR(60) { адрес фирмы } )

+------------+----------------------+------------------+ | company_id | name | address | +------------+----------------------+------------------+ | 101 | АО Рога и Копыта | Одесса, п/я 13 | | 105 | ТОО Добро пожаловать | Энск, 5-е авеню | | 107 | АОЗТ Сделай сам | Городская свалка | +------------+----------------------+------------------+

При такой структуре данной таблицы и при таком ее содержании оператор

UPDATE companies SET name = "ИЧП Мастер Безенчук" WHERE company_id = 101

приведет к исполнению триггера upd_trig. Причем, так как модифицироваться будет одна запись, то и процедура proc1, и proc2 будут исполнены по одному разу, причем вначале proc1, а затем proc2. Раздел AFTER и, соответсвенно, процедура proc2, будут исполнены уже после внесения изменений в базу данных. Но если оператор UPDATE модифицирует несколько записей, то процедура proc1 будет исполнена несколько раз (по одному разу для каждой модифицируемой записи), а процедура proc2 - только один раз - после внесения всех изменений. Так, оператор

UPDATE companies SET name = "ТОО Льдинка" WHERE company_id > 103

изменит название у двух компаний, то есть в одном запросе будут модифицированы поля name у двух записей. В результате, процедура proc1 будет исполнена дважды, а затем один раз будет вызвана процедура proc2. Если в операторе UPDATE предполагается изменение поля name, но ни одна запись не была модифицирована (не удовлетворила условию в разделе WHERE, например), то раздел FOR EACH ROW триггера не будет выполнен ни разу, тогда как разделы BEFORE и AFTER все равно сработают. Так, следующий оператор исполнит процедуру proc2 и ни разу не выполнит proc1:

UPDATE companies SET name = NULL WHERE company_id < 57

А следующий оператор вообще не приведет к выполнению триггера upd_trig, так как поле name данной таблицы не изменяется и его вообще нет в списке обновляемых полей:

UPDATE companies SET address = "Москва, Бутырка" WHERE company_id = 101

Имена для старого и нового значений записи

Вернемся к тем задачам, с которых мы начали разговор о триггерах. А именно, о необходимости удалять информацию о сотрудниках при удалении фирмы и о протоколировании всех изменений в таблице. Для того, чтобы реализовать эти задачи надо из триггера получить значения полей модифицируемой записи. Например, при удалении информации о фирме нам надо знать уникальный ключ удаляемой запии - поле companies_id, иначе мы не сможем определить сотрудников удаляемой фирмы.

В механизме триггеров предусмотрена возможность получения значений полей модифицируемой записи. Причем, можно получить значение записи как до модификации (старого значения), так и после (нового значения). Для этого, раздел "действие" в описании триггера надо начать с задания связанных имен записи:

REFERENCING OLD AS <связанное имя для старого значения> REFERENCING NEW AS <связанное имя для нового значения>

Указывать связанные имена можно в любом порядке. Если какое-то из имен не нужно (например, нам не требуется имя для старого значения), то его можно не указывать. Слово AS можно опускать. ¹³)

Примеры использования связанных имен:

CREATE TRIGGER del_trig DELETE ON items REFERENCING OLD del_rec FOR EACH ROW ( UPDATE tab2 SET total=total-del_rec.price )

CREATE TRIGGER upd_trig UPDATE OF name ON companies REFERENCING NEW newval REFERENCING OLD oldval FOR EACH ROW (EXECUTE PROCEDURE proc4(oldval.name, newval.name) ) AFTER ( EXECUTE PROCEDURE proc2 )

Итак, все-таки, напишем триггер, который будет срабатывать на удаление информации о фирме (таблица companies) и действие которого будет состоять в удалении из таблицы persons информацию о всех сотрудниках этой фирмы:

CREATE TABLE companies ( company_id SERIAL, { первичный ключ фирмы } ....)

CREATE TABLE persons (..., company INTEGER, { ссылка на фирму, где работает } ....)

CREATE TRIGGER del_comp DELETE ON companies REFERENCING OLD AS rec FOR EACH ROW (DELETE FROM persons WHERE persons.company = rec.company_id )

Теперь рассмотрим реализацию триггера для автоматического ведения протокола об изменениях имен у компаний. Требуется фиксировать, кто, когда и как модифицирповал название фирмы (поле name) в данной таблице. Пусть для этого заведем таблицу с протоколом, имеющую следующую структуру:

CREATE TABLE protocol ( company INTEGER, { идентификатор измененной записи } login CHAR(8), { ситемное имя пользователя } oldname CHAR(40), { старое имя фирмы } newname CHAR(40), { новое имя фирмы } when DATETIME YEAR TO SECOND { когда } )

Тогда, требуемый нам триггер будет выглядеть так:

CREATE TRIGGER upd_compname UPDATE OF name ON companies REFERENNCING NEW AS newcomp REFERENNCING OLD AS oldcomp FOR EACH ROW (INSERT INTO protocol (company, login, oldname, newname, when) VALUES (oldcomp.company_id, USER, oldcomp.name, newcomp.name, CURRENT)

Условия внутри триггера

Триггерным событием является как само выполнение оператора, изменяющего таблицу, так и непосредственное изменение какой-либо записи. Но можно наложить и дополнительное условие. Для этого перед набором операторов (которые, напомним, разделены запятой и заключены в круглые скобки) надо поставить условие:

........ WHEN ( <условие> ) ( <оператор>, <оператор>, ...), WHEN ( <условие> ) ( <оператор>, <оператор>, ...), ........

Для каждого из разделов BEFORE/FOR EACH ROW/AFTER можно указывать произвольное количество таких пар условие-операторы. Пример триггера с дополнительными условиями:

CREATE TRIGGER upd_price UPDATE OF price ON items REFERENCING OLD AS pre REFERENCING NEW AS post FOR EACH ROW WHEN (post.price < 0) (EXECUTE PROCEDURE proc1(post.price)) WHEN (post.price > pre.price * 2) (EXECUTE PROCEDURE proc2(ppost.price, pre.price))