Триггеры sql представляют собой специальный тип хранимых процедур, запускаемых сервером автоматически при изменении данных (DML) в таблице, с которой он связан. Триггеры подключаются к определенной таблице. Все производимые триггером изменения данных рассматриваются как одна транзакция.

В отличие от обычной хранимой процедуры/функции, триггер вызывается сервером неявно при возникновения определенного триггерного события. Кроме этого триггер SQL не имеет аргументов. С помощью триггера решаются следующие задачи:

• проверка корректности изменяемых данных и проверка сложных ограничений целостности данных, которые необходимо поддерживать;
• накопление статистической информации посредством фиксации сведений о внесимых изменениях;
• поддержка репликации.

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

Применение SQL триггеров связано с дополнительными затратами ресурсов сервера на операции добавления (trigger insert ), обновления (trigger update ) или удаления (trigger delete ) данных в таблице.

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

CREATE TRIGGER

Основной формат команды CREATE TRIGGER показан ниже:

CREATE TRIGGER trigger_name [ BEFORE | AFTER ] ON table_name begin end;

Момент запуска триггера определяется ключевыми словами BEFORE (триггер запускается перед выполнением связанного с ним событием; например, до добавления записи) или AFTER (после события). Если триггер вызывается до события, он может внести изменения в модифицируемую событием запись, если событие - не удаление записи. Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, с которой «связан» триггер, и т.п.).

Триггеры могут быть подключены не к таблице, а к представлению VIEW . В этом случае с их помощью реализуется механизм «обновляемого представления». При подключении триггера к представлению ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Триггер может быть вызван для каждой строки (FOR EACH ROW ), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT ).

Обозначение <список_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Cтарые значения не применимы для событий вставки, а новые – для событий удаления.

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

Некорректно написанные триггеры могут привести к серьезным проблемам, связанным с появлением блокировок. Триггеры способны длительное время блокировать ресурсы, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

MS SQL trigger

Синтаксис создания триггера в СУБД MS SQL имеет следующий вид:

CREATE TRIGGER trigger_name ON {WITH ENCRYPTION} [ [,] [,] ] [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement }

schema_name

Наименование схемы триггера DML. Действие триггеров DML ограничивается областью схемы таблицы или представления, для которых они созданы. schema_name не может указываться для триггеров DDL или триггеров входа.

trigger_name

Наименование триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов - за исключением того, что trigger_name не может начинаться с символов # или ##.

table_name | view_name

Таблица или представление, к которым подключен триггер.

Пример ms sql trigger

Для реализации триггера будут созданы две таблицы: test_table, test_log. К таблице test_table будет подключен триггер. При обновлении записей в таблице test_table триггер будет регистрировать в таблице test_log результаты изменений. Т.е. триггер будет вызываться по событию update.

Тестовая таблица test_table:

Create table dbo.test_table (id int not null, field1 varchar(255) null, field2 varchar(255) null, constraint pkTestTableID primary key (id));

Таблица журналирования test_log:

Create table dbo.test_log (id bigint identity(1,1) not null, table_name varchar(50) not null, oper varchar(15) not null, record_old xml null, record_new xml null, data datetime null, constraint pkTestLogID primary key (id));

Триггер обновления данных:

Trigger update create trigger dbo.trg_test_table_update on dbo.test_table for UPDATE as begin set nocount on -- переменные для хранения старых и новых данных declare @record_new xml; declare @record_old xml; -- в таблице deleted хранятся старые/удаленные данные set @record_old = (SELECT * FROM deleted FOR XML RAW, TYPE); -- в таблице inserted хранятся измененные (только что созданные) данные set @record_new = (SELECT * FROM inserted FOR XML RAW, TYPE); if (@record_new is not null) and (@record_old is not null) begin insert into dbo.test_log (table_name, oper, record_old, record_new, data) values ("test_table", "update", @record_old, @record_new, GETDATE()) end; end;

Добавим несколько строк в тестовую таблицу, которые будем обновлять для тестирование триггера:

Insert into dbo.test_table (id, field1, field2) values (1, "Кофе", "Nescafe"); insert into dbo.test_table (id, field1, field2) values (2, "Чай" , "Greenfield");

Проверяем работу триггера обновлением строк:

Проверяем таблицу журналирования test_log. Результат должен выглядеть так, как это представлено на скриншоте:

XML данные просматриваются и показывают, что таблица журналирования включает как старые, так и новые значения.

PostgreSQL trigger

Синтаксис создания триггера

CREATE TRIGGER trigger_name [ событие [ OR событие ]] ON table_name FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE function_name (аргументы)

В аргументе указывается наименование создаваемого триггера. При необходимости может быть указано наименование схемы.

{ BEFORE | AFTER }

Ключевое слово BEFORE означает, что trigger before и функция должна выполняться перед выполнением соответствующего события. Ключевое слово AFTER означает, что trigger after и функция вызывается после завершения операции, приводящей в действие триггер.

{ событие [ OR событие... ] }

В PostgreSQL поддерживаются следующие события . При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.

Наименование таблицы, модификация которой приводит к срабатыванию триггера.

FOR EACH { ROW | STATEMENT }

Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Использование ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.

EXECUTE PROCEDURE function_name

Наименование вызываемой функции с аргументами. На практике аргументы при вызове триггерных функций не используются.

Синтаксис определения триггерной функции

CREATE FUNCTION function_name () RETURNS trigger AS DECLARE -- объявления переменных BEGIN -- тело триггерной функции END; LANGUAGE plpgsql;

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

Наименование	Тип	Описание
NEW	RECORD	Новые значения полей записи, созданной командой INSERT или обновленной командой UPDATE, при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей. Переменная NEW доступна только при INSERT и UPDATE. Поля записи NEW могут быть изменены триггером.
OLD	RECORD	Старые значения полей записи, содержавшиеся в записи перед выполнением команды DELETE или UPDATE при срабатывании триггера уровня записи (ROW). Переменная OLD доступна только при DELETE и UPDATE. Поля записи OLD можно использовать только для чтения, изменять нельзя.
TG_NAME	name	Имя сработавшего триггера.
TG_WHEN	text	Операторы BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении.
TG_LEVEL	text	Строка ROW или STATEMENT в зависимости от уровня триггера, указанного в определении.
TG_OP	text	Строка INSERT, UPDATE или DELETE в зависимости от операции, вызвавшей срабатывание триггера.
TG_RELID	oid	Идентификатор объекта таблицы, в которой сработал триггер.
TG_RELNAME	name	Имя таблицы, в которой сработал триггер.

К отдельным полям записи NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names, OLD.rg.

Пример postgresql trigger

В примере реализована простая система логирования пользователей. Она следит за таблицей пользователей и все изменения регистрирует в таблице журналирования. Для примера будем создавать упрощенные таблицы.

Таблица пользователей:

CREATE TABLE "public".users (id int not null, name varchar (64), constraint pkUsersID primary key (id));

Таблица протоколирования

CREATE TABLE "public".logs (text varchar(256), data timestamp without time zone);

Триггерная функция

CREATE OR REPLACE FUNCTION "public".add_to_log() RETURNS TRIGGER AS $$ DECLARE v_action varchar(30); v_user varchar(64); v_retstr varchar(256); BEGIN IF TG_OP = "INSERT" THEN v_user = NEW.name; v_action:= "Add new user "; v_retstr:= v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = "UPDATE" THEN v_user = NEW.name; v_action:= "Update user "; v_retstr:= v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = "DELETE" THEN v_user = OLD.name; v_action:= "Remove user "; v_retstr:= v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql;

Триггерная функция без входящих параметров возвращает специальный тип TRIGGER . В функции в разделе DECLARE определены 3-и переменные. В теле функции выполняется проверка значения переменной TG_OP (внутренняя переменная триггера). В зависимости от транзакции определяем переменнаю v_user и формируется строка retstr, которая записывается в таблицу logs.

Переменные NEW и OLD - это собственно строки которые обрабатывает триггер. В случае INSERT переменная NEW будет содержать новую строку, а OLD будет пустая. В случае UPDATE обе переменные будут определены (соответствующими данными), а в случае DELETE переменная NEW будет пустая, OLD содержать удаляемую строку.

Сам триггер описывается на PL/pgSQL как:

Trigger insert & trigger update & trigger delete CREATE TRIGGER trg_user AFTER INSERT OR UPDATE OR DELETE ON "public".users FOR EACH ROW EXECUTE PROCEDURE add_to_log ();

Триггер trg_user будет выполняться после выполнения транзакций INSERT, UPDATE, DELETE для каждой строки и вызывать функцию add_to_log(). Теперь любые действия с таблицей users будут протоколироваться.

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

Добавление записи в таблицу пользователей insert into users (id, name) values (1, "Киса Воробьянинов"); -- Обновление записи в таблице пользователей update users set name = "Остап Бендер" where id = 1 -- Чтение пользователей select * from users -- Чтение журнала протоколирования. Должно быть 2 записи select * from logs

Oracle triggers

Синтаксис CREATE TRIGGER в Oracle имеет следующий вид:

Trigger before CREATE TRIGGER trigger_name BEFORE DELETE OR INSERT OR UPDATE ON table_name REFERENCING <список_псевдонимов> FOR EACH ROW WHEN (new.field_name > 0) DECLARE -- переменные, константы, курсоры и т.п. BEGIN -- блок PL/SQL END;

В тексте создания триггера может быть включено необязательное ограничение триггера, путем определения булевского выражения SQL в фразе WHEN . Выражение в фразе WHEN проверяется для каждой строки, затрагиваемой триггером. Если результат выражения ИСТИНА, то тело триггера исполняется. Если выражение ЛОЖЬ или NULL, то тело триггера не исполняется. Выражение в фразе WHEN должно быть выражением SQL, но не выражением PL/SQL, и не может включать подзапрос.

REFERENCING

Опция REFERENCING может использоваться в теле триггера для того, чтобы избежать конфликтов между корреляционными именами и именами таблиц, в случае, если таблица имеет имя "OLD" или "NEW". Такая ситуация редка и эта опция почти никогда не применяется.

В качестве примера можно рассмотреть таблицу с именем new. Следующее определение CREATE TRIGGER показывает триггер, ассоциированный с таблицей new , который использует опцию REFERENCING , чтобы избежать конфликтов между корреляционными именами и именем таблицы:

Trigger before CREATE TRIGGER trg_dummy BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN:newest.field2:= TO_CHAR (:newest.field1); END;

Оператор new переименован в newest с помощью опции REFERENCING , а затем использован в теле триггера.

Условные предикаты

Если триггер может быть вызван на исполнение более чем одним типом предложения DML (например, "INSERT OR DELETE OR UPDATE"), то в теле триггера можно использовать операторы INSERTING , DELETING и UPDATING , для выполнения различных участков кода в зависимости от условия. В коде внутри тела триггера вы можете использовать следующие условия:

IF INSERTING THEN . . . END IF; IF UPDATING THEN . . . END IF;

Первое условие будет выполняться в тех случаях, когда триггер был стартован при вставке строки в таблицу. Второе условие будет выполняться при обновлении строки таблицы.

В операторе UPDATING можно дополнительно использовать условие проверки имени обновляемого столбца. В качестве примера можно рассмотреть следующий код, в котором тело будет исполняться, если предложение UPDATE, возбудившее триггер, обновляет столбец SAL:

IF UPDATING ("SAL") THEN . . . END IF;

Oracle triggers отключение, включение

В Oracle триггер можно временно выключить, если имеет место одно из следующих условий:

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

Триггер по умолчанию включается в момент его создания. Чтобы отключить триггер, необходимо использовать команду ALTER TRIGGER с опцией DISABLE . Чтобы включить триггер, используйте команду ALTER TRIGGER с опцией ENABLE . Можно одновременно отключить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опцией DISABLE ALL TRIGGERS .

Отключение триггера ALTER TRIGGER TRG_Orders_INS DISABLE; -- подключение триггера ALTER TRIGGER TRG_Orders_INS ENABLE; -- отключение всех триггеров таблицы ALTER TABLE Orders DISABLE ALL TRIGGERS;

Для включения или отключения триггера с помощью команды ALTER TABLE, необходимо либо быть владельцем таблицы, либо иметь соответствующую привилегию.

Пример oracle trigger

Генератор последовательностей CREATE SEQUENCE seqID; -- таблица пользователей CREATE TABLE users (id int PRIMARY KEY not null, name varchar(50), phone varchar(15), dt date); -- trigger insert определяет идентификатор записи CREATE OR REPLACE TRIGGER trgAutonumber BEFORE INSERT ON users -- trigger before FOR EACH ROW BEGIN select seqID.NEXTVAL into:new.id from dual; END; -- trigger insert определяет дату записи CREATE OR REPLACE TRIGGER trgDate BEFORE INSERT ON users trigger before FOR EACH ROW BEGIN if:old.dt is null then:new.dt:= current_date; end if; END trgDate;

В следующем примере триггер trgDepartmentst_del_cascade выполняет каскадное удаление записей TRIGGER DELETE CASCADE . Триггер, подключенный к таблице departments, реализует ссылочное действие DELETE CASCADE по первичному ключу таблицы deptID:

Trigger after CREATE OR REPLACE TRIGGER trgDepartmentst_del_cascade AFTER DELETE ON departments FOR EACH ROW BEGIN /* После удаления строки из таблицы Departments удалить из таблицы Employees все строки, имеющие такое же значение deptID. */ DELETE FROM employees WHERE employees.deptID = :old.deptID; END;

Примечание: обычно код для DELETE CASCADE объединяют вместе с кодом для UPDATE SET NULL или UPDATE SET DEFAULT, чтобы учесть как обновления, так и удаления в одном триггере.

Создание генераторов

Генератор – это хранящаяся в БД программа, выдающая при каждом обращении к ней уникальное число.

Создание генератора:

CREATE GENERATOR <Имя генератора>

Начальное значение задается инструкцией:

SET GENERATOR <Имя генератора> TO <Начальное значение (целое число)>

CREATE GENERATOR GenStore

SET GENERATOR GenStore TO 1

Обращение к созданному генератору выполняется с помощью функции

GEN_ID (<Имя генератора>, <Шаг>)

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

Создание триггера:

CREATE TRIGGER <> FOR <>

{BEFORE | AFTER}

{UPDATE | INSERT | DELETE}

AS <Тело триггера>

Описатели ACTIVE | INACTIVE определяют активность триггера сразу после его создания. По умолчанию действует ACTIVE.

Описатели BEFORE | AFTER задают момент начала выполнения триггера до или после наступления соответствующего события, связанного с изменением записей.

Описатели UPDATE | INSERT | DELETE определяют, при наступлении какого события вызывается триггер – при редактировании, добавлении или удалении записей.

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

Удаление триггера:

DROP TRIGGER <Имя триггера>

Изменение триггера:

Для доступа к значениям столбца используются инструкции формата:

OLD.<Имя столбца> - обращается к старому (до внесения изменений) значению столбца,

NEW.<Имя столбца> - обращается к новому (после внесения изменений) значению столбца.

Создание триггера для занесения в ключевой столбец уникальных значений

CREATE TABLE Store

(S_Code INTEGER NOT NULL ,

PRIMARY KEY (S_Code));

CREATE GENERATOR GenStore

SET GENERATOR GenStore TO 1

CREATE TRIGGER CodeStore FOR Store

NEW.S_Code = GEN_ID (GenStore, 1);

При добавлении к таблице Store новой записи ключевому столбцу S_Code этой записи автоматически присваивается уникальное значение. Это обеспечивается обращением GEN_ID к генератору GenStore.

Реализация каскадного удаления записей с участием триггера

CREATE TABLE Store

(S_Code INTEGER NOT NULL ,

PRIMARY KEY (S_Code));

CREATE TABLE Cards

(C_Code INTEGER NOT NULL,

C_Code2 INTEGER NOT NULL,

PRIMARY KEY (C_Code));

CREATE TRIGGER DeleteStore FOR Store

DELETE FROM Cards WHERE Store.S_Code = Cards.C_Code2;

После удаления записи в таблице Store буду автоматически удалены все соответствующие записи в таблице Cards.

Замечание : для таблиц не должны действовать ограничения ссылочной целостности, заданные на физическом уровне.

Обновление столбцов связи (ключевых столбцов) связанных таблиц, заключающееся в том, что при изменении значения столбца связи главной таблицы соответственно изменяются значения столбца связи всех связанных записей подчиненной таблицы.

CREATE TRIGGER ChangeStore FOR Store

IF (OLD.S_Code <> NEW.S_Code)

THEN UPDATE Cards

SET C_Code2 = NEW.S_Code

WHERE C_Code2 = OLD.S_Code;

При изменении столбца S_Code, используемого для связи главной таблицы Store с подчиненной таблицей Cards, автоматически изменяются значения столбца связи C_Code2 соответствующих записей подчиненной таблицы.

Чтобы столбец главной таблицы можно было редактировать, по нему не должен быть создан ключ.

Триггер – это подпрограмма, похожая на процедуру БД, автоматически вызываемая СУБД при изменении, удалении или добавлении записи в таблице. К триггерам невозможно обратиться из программы, передать им параметры или получить от них результат. Наиболее часто триггеры применяются для поддержания ссылочной целостности и каскадных операций в БД. Ссылочные спецификации, определяющие каскадные действия при удалении и обновлении и создаваемые при объявлении таблиц, также реализуются через триггеры, однако текст этих триггеров не редактируется.

Назначение триггеров

Предотвращение изменения (например, предотвратить счета от изменений после того как они разосланы).
. Журналирование изменения (например, хранить копии старых данных).
. Аудит изменений (например, вести лог пользователей и ролей, участвующих в изменениях).
. Фиксация изменений (например, обеспечить, чтобы все изменения датировались по часам сервера, а не клиента).
. Реализация бизнес-правил.
. Репликация данных (например, хранить записи всех изменений, которые будут отправлены в другой базе данных более поздней версии).
. Повышение производительности (например, обновление сальдо после каждой детали сделки, для ускорения запросов).

Объявление триггеров

CREATE TRIGGER {BEFORE|AFTER } {DELETE|INSERT|UPDATE [OF ]} ON REFERENCING {OLD {[ROW ]|TABLE [AS ] } NEW {ROW|TABLE } [AS ] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC ]

[END ]

Ключевые слова

. BEFORE|AFTER – время запуска триггера – до | после операции обновления.
. DELETE|INSERT|UPDATE = событие срабатывания триггера.
. FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
. FOR EACH STATEMENT – для всей команды (действует по умолчанию).
. REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

Ограничения триггеров

Тело триггера не может содержать операторов:
. Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
. Обработки транзакций (COMMIT, ROLLBACK)
. Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
. Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
. Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
. Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
. Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @ .
. В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
. В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
. Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
. Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

Недостатки триггеров

Сложность. Размещение некоторых действий над данными в БД усложняет ее проектирование, реализацию и администрирование.
. Скрытность функциональных возможностей от пользователя. Трудно производить модернизацию приложения, когда скрыты некоторые возможности.
. Влияние на производительность. При небольшом числе триггеров увеличивается время обработки данных.

Изменение и удаление триггеров

Для удаление триггера используется оператор DROP TRIGGER
. Для изменения триггера используется оператор ALTER TRIGGER …
. Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE } {BEFORE|AFTER } {INSERT|DELETE|UPDATE } [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION ] {FOR|AFTER|INSTEAD OF } {INSERT|UPDATE|DELETE }
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
. В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER

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

Триггеры создаются отдельно для каждой таблицы и располагаются в обозревателе объектов в папке «Triggers». Создадим триггеры для таблицы «teachers». Папка «Triggers» будет входить в состав таблицы «teachers»:

Создадим триггер, выводящий сообщение «Запись добавлена» при добавлении записи в таблицу. Создадим новый триггер, щёлкнув ПКМ по папке «Triggers» в таблице «teachers» и в появившемся меню выбрав пункт «New Trigger». Появится следующее окно с новым триггером:

Рассмотрим структуру триггеров:

1) Область определения имени функции (Trigger_Name);

2) Область, показывающая для какой таблицы создаётся триггер (Table_Name);

3) Область, показывающая когда выполнять триггер (INSERT – при создании записи в таблице, DELETE – при удалении и UPDATE – при изменении) и как его выполнять (ALTER – после выполнения операции, INSTEAD OF – вместо выполнения операции);

4) Тело триггера, содержит команды языка программирования запросов TSQL.

В окне нового триггера наберем код:

CREATE TRIGGER tr_add

ON dbo.teachers

PRINT "Новая запись добавлена"

Из рисунка видно, что создаваемый триггер «Индикатор добавления» выполняется после добавления записи (AFTER INSERT) в таблицу «dbo.teachers» (ON dbo.teachers). После добавления записи триггер выведет на экран сообщение "Новая запись добавлена" (PRINT "Новая запись добавлена"). Выполним набранный код, нажав кнопку на панели инструментов. В нижней части окна с кодом появиться сообщение «Command(s) completed successfully.».

Проверим, как работает новый триггер. Создадимновый пустой запрос и в нём наберем следующую команду для добавления новой записи в таблицу «dbo.teachers»:

insert into dbo.teachers

"Сидоров"

Выполним набранную команду, нажав кнопку на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение «Новая запись добавлена»

Аналогичным образом создаются триггеры с UPDATE и DELETE.

Задание: в соответствии со своей темой создайте в SQL Server Management Studio представления (3-4), запросы(3-4), хранимые процедуры(3-4), пользовательские функции(3-4), триггеры (INSERT, UPDATE и DELETE ). Используйте при создании операторы условия и циклы.

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

Триггер всегда работает с текущей записью и реализует конкретное действие.

Триггеры могут выполняться до наступления события (параметр BEFORE) или после наступления события (параметр AFTER).

Триггеры различают по направлению действия:

• INSERT - на добавление записи;
• UPDATE - на редактирование записи;
• DELETE - на удаление записи.

Триггер создается для конкретной таблицы и принадлежит ей. Если таблица имеет несколько триггеров одного направления действия, то время их срабатывания определяется в первую очередь параметрами BEFORE и AFTER , а при одинаковом значении параметра наступления события - параметром POSITION с указанием номера (порядка) срабатывания триггера.

При работе с триггерами следует иметь в виду, что:

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

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

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

Для создания триггера используется оператор CREATE TRIGGER.

Формат оператора

CREATE TRIGGER FOR

[ ACTIVE | INACTIVE ]

[ BEFORE |AFTER]

[ INSERT | UPDATE DELETE ]

[ POSITION ]

Назначение опций:

ACTIVE - триггер активен, т. е. при обращении к указанной таблице выполняется процедура, записанная в стеле триггерам

INACTIVE - триггер пассивен, т. е. триггер создан и хранится на сервере, но при обращении к указанной таблице стело триггера> не выполняется;

BEFORE - до наступления события;

AFTER - определяет время срабатывания после наступления события;

INSERT - определяет для триггера событие добавления записи в таблицу;

UPDATE - определяет для триггера событие редактирования записи в таблице;

DELETE - определяет для триггера событие удаления записи из таблицы;

POSITION - определяет номер (позицию) срабатывания триггера внутри определенного события срабатывания (BEFORE или AFTER).

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

При написании стела триггера> дополнительно можно использовать ключевые слова OLD (до события) и NEW (после события) с последующим указанием имени поля.

Так как в удаленной базе данных все изменения в таблицах (добавление, редактирование и удаление записи) производятся в выборках (в оперативной памяти), то, например, при изменении значения поля можно обратиться как к старому (до изменения) значению поля - ОЬО.симя поля>, так и к новому (после изменения) значению поля - NEW-симя полях Если в указанное поле изменение не вносилось, то ОЬО.симя поля> будет равно NEW.chmb полях

Пример 6.10. Создание триггера.

CREATE TRIGGER T_COMPCODE FOR COMPOSERS

ACTIVE BEFORE INSERT POSITION 0

new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data born is not null) then begin

new.actuallyage = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =(COMPOSERS.data_day-COMPOSERS. data born) /365; else new.age=null; end

Триггер срабатывает до наступления события добавления новой записи и вычисляет возраст композитора. Вычисленный возраст записывает в поле «actually_age ».

Изменение триггера

Для изменения триггера используется команда ALTER TRIGGER , имеющая аналогичный формат и аналогичный принцип работы, что и команда ALTER PROCEDURE для изменения тела хранимой процедуры.

Формат команды

ALTER TRIGGER FOR [ ACTIVE I INACTIVE

BEFORE I AFTER

INSERT I UPDATE DELETE

[ POSITION 1

После выполнения оператора ALTER TRIGGER старое определение триггера заменяется новым определением. Старое определение триггера восстановить нельзя.

Пример 6.11. Редактирование триггера.

ALTER TRIGGER T_COMPCODE FOR COMPOSERS ACTIVE BEFORE INSERT POSITION 5 AS

new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data_born is not null) then begin

new.actually_age = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COM POSERS.dataday is not null) then COM POSERS.age =

(COMPOSERS.data_day-COMPOSERS.data born) /365; else new.age=null; end

else begin new.age=null; new.actually_age=null; end end

В триггер, созданный в примере 6.10, внесено изменение: задана новая позиция (POSITION ) срабатывания триггера - 5.

Удаление триггера

Для удаления триггера используют команду

Пример 6.12. Удаление триггера.

DROP TRIGGER T_COMPCODE

Восстановить удаленный триггер нельзя.

Использование триггера в каскадных воздействиях

Как отмечалось выше, предусмотрено два вида каскадных воздействий: каскадное изменение и каскадное удаление. Поэтому для каждой родительской таблицы надо предусмотреть два триггера: один - для каскадного изменения, второй - для каскадного удаления. При этом условия ссылочной целостности должны быть удалены.

Пример 6.13. Каскадное удаление записей.

При удалении фамилии из родительской таблицы ГАМ необходимо удалить соответствующие фамилии (по ключам фами-

лии) во всех дочерних таблицах (в примере таблицы AUTHOR и BOOK).

CREATE TRIGGER DEL FAM FOR FAM ACTIVE

DELETE FROM AUTHOR

WHERE FAM.KEYFAM = AUTHOR. KEYFAM; DELETE FROM BOOK

WHERE FAM.KEY FAM = BOOK.KEY FAM;

Пример 6.14. Каскадное редактирование записей.

При изменении значения ключевого поля (KEY FAM) в родительской таблице FAM необходимо изменить соответствующие значения внешних ключей во всех дочерних таблицах (в примере таблицы AUTHOR и BOOK).

CREATE TRIGGER UPD FAM FOR FAM ACTIVE

BEFORE UPDATE AS

IF (OLD.KEYFAM NEW.KEY FAM) THEN BEGIN

UPDATE AUTHOR SET KEY FAM = NEW.KEY FAM

WHERE KEY FAM - OLD.KEY FAM; UPDATE BOOK SET KEYFAM - N EW. KEYFAM

WHERE KEY FAM = OLD.KEY FAM;

Особенности использования каскадных воздействий:

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

Обеспечение достоверности данных с помощью триггера

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

а. Обеспечение уникальности значения поля

Как правило, для этих целей используют генератор. Работу с генераторами см. п. 6.3. Предварительно создается генератор, а затем имя генератора указывают в теле триггера.

Пример 6.15. Заполнение поля первичного ключа.

Написать триггер по добавлению уникального значения первичного ключа KEYFAM. Генератор GFAM уже создан.

CREATE TRIGGER К РАМ FOR FAM

N EW. KEYFAM = GEN_ID(G_FAM, 1);

Пример 6.16. Заполнение информационного поля.

CREATE TRIGGER TCOMPDATE FOR COMPOSERS

ACTIVE BEFORE UPDATE POSITION 0

if(COMPOSERS.data born is not null) then begin

new.actually_age = (CAST("NOW" AS

DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =

(COM POSERS.data_day-COMPOSERS.data_born)/365; else new.age=null; end

else begin new.age=null; new.actually_age=null; end

В данном примере вычисляется возраст человека и заполняется поле actually age.

Ведение журнала аудита с помощью триггера

В удаленных базах данных особый интерес представляет ведение журнала изменений таблиц базы данных с целью определения источника недостоверных данных. При ведении журнала изменений в специальной таблице фиксируется:

• выполненное действие над таблицей;
• новое значение поля;
• старое значение поля;
• дата внесения изменения;
• фамилия, имя и отчество пользователя (USER NAME );
• номер (имя) рабочей станции.

Пример 6.17. Автоматическое заполнение журнала аудита.

CREATE TRIGGER AFTJNS_DOGS FOR DOGS ACTIVE AFTER INSERT POSITION 0 AS begin

insert into log (act, table_name,record_id) values("INSERT","DOGS",DOGS. ID); end

CREATE TRIGGER AFT_UPD_DOGS FOR DOGS ACTIVE AFTER UPDATE POSITION 0 AS begin

insert into log (act,table_name,record_id) values(’UPDATE’,"DOGS’,DOGS.ID); end

CREATE TRIGGER AFT DEL DOGS FOR DOGS ACTIVE AFTER DELETE POSITION 0 AS

insert into log (act,table_name,record_id) values(’DELETE","DOGS’,DOGS.ID); end

В этом примере для таблицы DOGS созданы три триггера (по одному на каждое событие INSERT, UPDATE и DELETE). Каждый из триггеров добавляет в таблицу аудита log одну строку, которая содержит поля «выполненное действие», «имя таблицы» и «номер записи». При желании количество полей в таблице аудита можно увеличить.