Последнее обновление: 09.11.2017

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

Формальное определение триггера:

CREATE TRIGGER имя_триггера ON {имя_таблицы | имя_представления} {AFTER | INSTEAD OF} AS выражения_sql

Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

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

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

AFTER : выполняется после выполнения действия. Определяется только для таблиц.

INSTEAD OF : выполняется вместо действия (то есть по сути действие - добавление, изменение или удаление - вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Определим триггер, который будет срабатывать при добавлении и обновлении данных:

USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id = (SELECT Id FROM inserted)

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

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:

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

Для удаления триггера необходимо применить команду DROP TRIGGER :

DROP TRIGGER Products_INSERT_UPDATE

Отключение триггера

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

DISABLE TRIGGER Products_INSERT_UPDATE ON Products

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

ENABLE TRIGGER Products_INSERT_UPDATE ON Products

Приднестровский Государственный Университет

по дисциплине «Базы и банки данных »

г. Тирасполь

ЛАБОРАТОРНАЯ РАБОТА № 8

Тема: «Триггеры: создание и применение»

ЦЕЛЬ РАБОТЫ: научиться работать с триггерами.

Теоретическая часть.

Определение триггера в стандарте языка SQL

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

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

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

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

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

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

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

· накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

· поддержка репликации.

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

<Определение_триггера>::=

CREATE TRIGGER имя_триггера

BEFORE | AFTER <триггерное_событие>

ON <имя_таблицы>

<тело_триггера>

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

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

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

Реализация триггеров в среде MS SQL Server

В реализации СУБД MS SQL Server используется следующий оператор создания или изменения триггера:

<Определение_триггера>::=

{CREATE | ALTER} TRIGGER имя_триггера

ON {имя_таблицы | имя_просмотра }

{ { FOR | AFTER | INSTEAD OF }

{ [ DELETE] [,] [ INSERT] [,] [ UPDATE] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

sql_ оператор [...n]

{ {FOR | AFTER | INSTEAD OF } { [,]

}

[ WITH APPEND]

[ NOT FOR REPLICATION]

{ IF UPDATE(имя _ столбца )

[ {AND | OR} UPDATE(имя _ столбца )] [...n]

IF (COLUMNS_UPDATES(){ оператор _ бит _ обработки }

бит_маска_изменения)

{оператор_бит_сравнения }бит_маска [...n]}

sql_оператор [...n]

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

Рассмотрим назначение аргументов из команды CREATE | ALTER TRIGGER.

Имя триггера должно быть уникальным в пределах базы данных. Дополнительно можно указать имя владельца .

При указании аргумента WITH ENCRYPTION сервер выполняет шифрование кода триггера, чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной .

Типы триггеров

В SQL Server существует два параметра, определяющих поведение триггеров:

· AFTER. Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера, то будут отклонены и пользовательские изменения. Можно определить несколько AFTER-триггеров для каждой операции (INSERT, UPDATE, DELETE). Если для таблицы предусмотрено выполнение нескольких AFTER-триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER-триггерами.

· INSTEAD OF. Триггер вызывается вместо выполнения команд. В отличие от AFTER-триггера INSTEAD OF-триггер может быть определен как для таблицы, так и для просмотра. Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD OF-триггер.

Триггеры различают по типу команд, на которые они реагируют.

Существует три типа триггеров:

· INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT.

· UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE.

· DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF } { [,] определяют, на какую команду будет реагировать триггер. При его создании должна быть указана хотя бы одна команда. Допускается создание триггера, реагирующего на две или на все три команды.

Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.

При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.

Конструкция AS sql_оператор[...n] определяет набор SQL - операторов и команд, которые будут выполнены при запуске триггера.

Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как:

· создание, изменение и удаление базы данных;

· восстановление резервной копии базы данных или журнала транзакций.

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

Программирование триггера

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

· команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;

· команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;

· команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

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

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

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

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE, вызвавших выполнение триггера, можно использовать функцию COLUMNS_UPDATED(). Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение "1", то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца).

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

DROP TRIGGER {имя_триггера} [,...n]

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

Практическая часть .

Для того, чтобы создать триггер необходимо:

После выбора Создать триггер появляется заготовка для ввода текста вашего триггера

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

Пример 1.

Создать триггер для обработки операции удаления записи из таблицы Kafedrs , например, такой команды:

Для кафедры, код которой указан при удалении записи, необходимо проверить, а нет ли сотрудников, работающих на этой кафедре. Иначе запись с этой кафедрой просто нельзя удалять. Таблицы Kafedrs и Sotrudniki связаны, и поэтому база данных сама не позволит это сделать. Удалим связь между таблицами. Для этого зайдем в Диаграммы баз данных -> Diagram_1 и удалим связь:

Сохраним изменения.

Теперь свободно можем удалить запись с названием кафедры, даже если в таблице Sotrudniki есть сотрудники, работающие на этой кафедре. Напишем триггер, который этого не допустит. Триггер будет иметь следующий код:

Теперь, если попытаться удалить кафедру, на которой работает хотя бы один сотрудник, то появится сообщение:

Пример 2.

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

Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если стаж работы сотрудника больше его возраста. Представленный триггер настроен на обработку только одной добавляемой записи.

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

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

Триггер откроется на изменения, об этом будет свидетельствовать слово ALTER вместо CREATE перед именем триггера. Допишем в коде, через запятую, после слова INSERT слова UPDATE.

Выполните запрос на обновления триггера.

Теперь, при некорректном вводе данных будет появляться следующее сообщения:

Пример 4.

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

Добавим в таблицу Sotrudniki новое поле Is_deleted, которое будет принимать значение True или False (подлежит сотрудник удалению или нет). Укажем ему тип bit и значение по умолчанию 0 (False).

Создадим новый триггер для таблицы Sotrudniki со следующим кодом.

Задание для самостоятельной работы.

1. Ознакомьтесь с теоретическим материалом.

2. Выполните задания из практической части.

3. Удалите связь между таблицей Sotrudniki и Dolghnosti. Напишите триггер не позволяющий удалять запись их Dolghnosti , если есть хотя бы один сотрудник, занимающий эту должность.

4. Напишите триггер, не позволяющий сотруднику, чей стаж меньше 3 лет занимать какую либо должность, например старшего преподавателя.

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

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

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

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

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

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

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 и 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, чтобы учесть как обновления, так и удаления в одном триггере.

Платформа SQL Server поддерживает ядро стандарта ANSI с добавлением триггеров типа INSTEAD OF и проверки изменения столбцов. Эта платформа не поддерживает предложения REFERENCING и WHEN. Синтаксис приводится ниже.

{CREATE | ALTER} TRIGGER имя_триггера ON имя_таблицы

{FOR | AFTER | INSTEAD OF} { [,] [,] }

[…]] блок_кода

(CREATE | ALTER) TRIGGER имя_триггера

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

ON имя_таблицы

Объявляется таблица или представление, от которых зависит триггер. В представлениях могут быть определены триггеры INSTEAD OF, если только эти представления можно обновлять и они не содержат предложения WITH CHECK.

WITH ENCRYPTION

Текст инструкции CREATE TRIGGER шифруется так, как это определено в таблице syscomments. Этот параметр полезно использовать для защиты интеллектуальной собственности. Предложение WITH ENCRYPTION не дает использовать триггер в схеме репликации SQL Server.

FOR | AFTER INSTEAD OF

Указывает, когда должен запускаться триггер. (Ключевые слова FOR и AFTER являются синонимами.) Предложение AFTER показывает, что триггер запускается только после запуска успешного выполнения операции по модификации данных (и других каскадно запускаемых действий и проверок ограничений). Триггер INSTEAD OF сходен с триггером BEFORE стандарта ANSI в том, что код триггера может полностью заменить операцию по модификации данных. При этом триггер запускается вместо операции по модификации, которая запустила триггер. Триггеры типа INSTEAD OF DELETE нельзя использовать, если удаление вызывает каскадные действия. Доступ к столбцам TEXT, NTEXT или IMAGE имеют только триггеры INSTEAD OF.

WITH APPEND

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

NOT FOR REPLICATION

Предотвращается запуск триггера при выполнении операции по модификации данных, инициированной встроенной системой репликации SQL Server.

IF UPDATE (столбец) [{AND | OR) РОАТЕ(столбец)] […]

Позволяет выбрать конкретный столбец, запускающий триггер. Триггеры, специфичные для столбца, запускаются только при операциях UPDATE и INSERT, но не при операциях DELETE. Если операция UPDATE или INSERT применяется к столбцу, не входящему в список, триггер не запускается.

Платформа SQL Server позволяет использовать несколько триггеров для одной операции по манипуляции в таблице или представлении. Таким образом, можно использовать сразу три триггера UPDATE в одной таблице. В таблице можно использовать несколько триггеров AFTER. Порядок их выполнения является неопределенным, однако первый и последний триггер можно указать явным образом, при помощи хранимой системной процедуры spsettriggerorder. В любой таблице допускается использовать только один триггер INSTEAD OF на инструкцию INSERT, UPDATE или DELETE.

В SQL Server в одной инструкции по созданию триггера можно определить любую комбинацию триггеров, разделив их запятыми. (При этом один и тот же код запускается для каждой инструкции в определении комбинации.)

Платформа SQL Server неявно использует для триггеров форму FOR EACH STATEMENT стандарта ANSI.

При запуске триггера SQL Server записывает значения в две важные псевдотаблицы: deleted и inserted. Они соответственно эквивалентны псевдотаблицам before и after, описанным ранее в разделе «Общие правила», относящемся к стандарту ANSI. Эти таблицы по структуре идентичны таблице, в которой создан триггер, за исключением того, что они содержат старые данные, до выполнения операции по модификации (deleted), и новые значения, имеющиеся в таблице после этой операции (inserted).

При указании предложения AS IF UPDATE (столбец) производится проверка на выполнение операции INSERT или UPDATE в данном столбце или столбцах; это предложение аналогично конструкции с/РШЩстолбец) в стандарте ANSI. Можно указать несколько столбцов, добавив отдельные предложения (УРОЛЩстолбец). Если за предложением AS IF UPDATE (столбец) поставить блок кода Transact-SQL BEGIN…END, можно выполнить в триггере несколько операций Transact-SQL. Это предложение функционально эквивалентно операции IF … THEN … ELSE.

Помимо вмешательства в выполнение операций по модификации данных, показанного в примере на ANSI SQL, платформа SQL Server позволяет выполнять другие виды операций при операции по модификации данных. В следующем примере мы решили, что таблица sales_archive_2002 более не используется, и тот, кто попытается вставить в нее данные, получит уведомление о данном ограничении.

Платформа SQL Server не разрешает использовать следующие инструкции в блоке кода Transact-SQL триггера: ALTER, CREATE, DROP, DENY, GRANT, REVOKE, LOAD, RESTORE, RECONFIGURE и TRUNCATE. Кроме того, не разрешаются инструкции DISK и команда UPDA ТЕ STA TISTICS.

Платформа SQL Server позволяет запускать рекурсивные триггеры, используя параметр recursive triggers хранимой системной процедуры sp_dboption. Рекурсивные триггеры в результате своего выполнения запускают самих себя. Например, если триггерINSERT в таблице Т1 выполняет операцию INSERT в таблице Т1, он может выполнить рекурсивную операцию. Поскольку рекурсивные триггеры могут быть опасными, они по умолчанию отключены.

Также SQL Server позволяет использовать вложенные триггеры, до 32 уровней вложенности. Если любой из вложенных триггеров выполняет операцию ROLLBACK, то последующие триггеры не запускаются. Пример вложенных триггеров: Триггер в таблице Т1 запускает операцию над таблицей Т2, в которой также есть триггер, который запускает операцию над таблицей ТЗ. Запуск триггеров отменяется, если формируется бесконечный цикл. Вложенные триггеры можно разрешить при помощи параметра nested triggers хранимой процедуры sp_configure. Если вложенные триггеры отключены, то рекурсивные триггеры также отключены, независимо от соответствующего параметра хранимой процедуры sp_dboption.

В следующем примере мы хотим переадресовать пользовательские действия над таблицей people, особенно транзакции, связанные с обновлением, таким образом, чтобы изменения строк таблицы people записывались вместо этого в таблицу peoplejreroute. (Более сложная форма таблицы people показана в пункте «SQL Server» раздела «Инструкция CREATE/ALTER TABLE».) Наш триггер обновлений будет регистрировать все изменения столбцов 2, 3 и 4 таблицы people и записывать их в таблицу peoplejreroute. Также триггер будет записывать, какой пользователь выполнял транзакцию-обновление и в какое время.

Отметьте, что инструкции CREATE в SQL Server позволяют использовать отложенное разрешение имен (deferred name resolution). Это означает, что команда обрабатывается даже в том случае, если она ссылается на еще не существующий объект базы данных.

Уже много статей в интернете есть про sql триггеры, но добавлю еще одну с адекватными примерами, что бы закрепить материал для тех, кто «в теме» и что бы лучше понять материал тем, кто только начал постигать «дзен sql». Заодно и создам дискуссию по теме.

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

Из работы над ними вынесли один ценный опыт - следить за приоритетами и статистикой. Что это значит? Все просто: если у Вас блог и у него 2-3-4-10012 млн посетителей в сутки, а статьи пишутся всего 1-2-3-3435 раз в сутки (на порядок меньше чем число просмотров), то скорость сохранения статьи (и сложность этого) относительно скорости показа статьи может быть пропорционально меньше. Чем больше показываем, тем критичен именно показ, а не сохранение статьи/страницы/таблицы. Что не означает, что и расслабляться можно. Сохранение статьи за 3-5-10 секунд в блоге - это в рамках адекватности, но генерация страницы за срок более 2 секунды (+ пока скрипты и стили с картинками подгрузятся) - это на грани «какой тормознутый сайт, почитаю что-то иное», а еще хуже «пойду куплю в другом месте».

Если мы возьмем среднестатистический сайт с голосовалкой/кармой, комментариями, счетчиком показа страницы и т.п., то многим разработчикам сразу в голову приходят конструкции вроде SELECT count(*) FROM comment WHERE comment.page=page_id. Ну подумаешь на каждую статью посчитать сумму рейтинга, сумму комментариев. А, у нас на главной по 10 статей из каждого раздела. При посещаемости в 10 человек в секунду, на среднем VPS, можно себе позволить по 60-100 запросов к sql на страницу (привет, битрикс).

Но к черту лирику (достал уже, наверное). Голые данные:

Таблица blog

CREATE TABLE IF NOT EXISTS `blog` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `text` text NOT NULL, `creation` datetime NOT NULL, `modification` datetime NOT NULL, `img` varchar(128) NOT NULL DEFAULT "default.png", `status` tinyint(4) NOT NULL DEFAULT "2", `user_id` int(11) NOT NULL, `rate` int(11) NOT NULL, `relax_type` tinyint(4) NOT NULL, `timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint(1) NOT NULL DEFAULT "0", `views` int(11) NOT NULL DEFAULT "0", `comment` int(11) NOT NULL, `url` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`), KEY `country_id` (`country_id`), KEY `user_id` (`user_id`), KEY `status` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

Таблица comments

CREATE TABLE IF NOT EXISTS `comments` (`owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", PRIMARY KEY (`id`), KEY `owner_name` (`owner_name`,`owner_id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

Как видим, в таблице блога у каждой статьи есть счетчик комментариев (поле comment).
Обычная практика:
1. Добавили комментарий - увеличили счетчик для блога
2. Удалили/скрыли комментарий - уменьшили счетчик.
Делать это в коде удобно и привычно, но есть более удобный инструмент - триггеры.

И так, у нас есть 2 события (на самом деле 3): создание комментария и его удаление (третье событие - это изменение его статуса («удаление», бан и т. п.).
Рассмотрим только создание и удаление, а изменение статуса пусть будет домашним заданием.

В примере есть одна особенность: комментарии могут быть к нескольким типам статей.

Создание комментария:

CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments` FOR EACH ROW BEGIN // у пользователя в личном кабинете посчитаем сколько он комментариев написал UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // определяем к чему относится комментарий и сразу увеличиваем счетчик в данных таблицах CASE NEW.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog`.id = NEW.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; END CASE; // тут мы облегчаем себе работу с лентами новостей // url статьи сразу пишем, что бы ПОТОМ не делать выборок лишних CASE NEW.`owner_name` WHEN "Blog" THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`.id= NEW.`owner_id`); WHEN "Article" THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET userurl = ``; END CASE; // название статьи сразу пишем, что бы ПОТОМ не делать выборку CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`); WHEN "Article" THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET usertitle = ` `; END CASE; INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle); END

Аналогично и удаление комментария:

CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments` FOR EACH ROW BEGIN UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; END CASE; END

И так, что получили:
1. При вставке комментария у нас автоматически средствами sql сервера посчиталась сумма комментариев у конкретного объекта комментирования (статья, страница, заметка)
2. Мы сформировали ленту новостей (привет всем соцсетям и т. п.)
3. При удалении комментария у нас происходит вычет всех данных.
4. Мы не использовали средства фреймворка.
5. Выборка всех нужных данных происходит быстро (всего 1 запрос при показе страницы, за исключением прочих «левых» данных на ней.)

А еще у нас стоит sphinx который периодически делает выборки статей, которые изменились за последнюю минуту. Для этого в блоге есть поле modification.

Добавлен триггер:

CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

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

CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

При изменении данных - обновим поисковый индекс тоже.

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

UPD: Холивар посвященный целесообразности усложнения структуры БД объявляется открытым.