Или используя предложение IF UPDATE
WITH ENCRYPTION имеет тот же смысл, что и для хранимых процедур, он скрывает исходный текст тела триггера.
{ FOR | AFTER | INSTEAD OF} – указывает, когда должен запускаться триггер. Ключевые слова FOR и AFTER являются синонимами. Предложение AFTER показывает, что триггер запускается только после успешного выполнения операции по модификации данных (и других каскадно запускаемых действий и проверок ограничений). Триггер INSTEAD OF может полностью заменить операцию по модификации данных. При этом триггер запускается вместо операции по модификации, которая запустила триггер. Триггер INSTEAD OF DELETE нельзя использовать, если удаление вызывает каскадные действия. Доступ к столбцам TEXT или IMAGE имеют только триггеры INSTEAD OF .IF UPDATE (имя_столбца) [{AND | OR} UPDATE (имя_ столбца)...] – позволяет выбрать конкретный столбец, запускающий триггер. Триггеры, специфичные для столбца, запускаются только при операциях INSERT или UPDATE, но не DELETE.Конструкции FOR {INSERT, UPDATE, DELETE} определяют, на какую команду будет реагировать триггер. При его создании должна быть указана хотя бы одна команда. Допускается создание триггера, реагирующего на две или на все три команды.
Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление «мертвых» блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.
В большинстве СУБД действуют следующие ограничения:
- Нельзя использовать в теле триггера операции создания объектов базы данных (новой базы данных, новой таблицы, новой хранимой процедуры, нового триггера, новых представлений).
- Нельзя использовать в триггере команду удаления объектов DROP для всех типов базовых объектов базы данных.
- Нельзя использовать в теле триггера команды изменения базовых объектов ALTER TABLE, ALTER DATABASE.
- Нельзя изменять права доступа к объектам базы данных, т.е. выполнять команду GRAND или REVOKE.
- Нельзя создать триггер для представления (VIEW) .
- В отличие от хранимых процедур триггер не может возвращать никаких значений, он запускается автоматически сервером и не может связаться самостоятельно ни с одним клиентом.
- Внутри триггера не допускается выполнение восстановления резервной копии БД или журнала транзакций.
Выполнение этих команд не разрешено, так как они не могут быть отменены в случае отката транзакции, в которой выполняется триггер.
Преимущества использования триггеров:
1. Триггеры всегда выполняются при совершении соответствующих действий. Разработчик продумывает использование триггеров при проектировании базы данных и может больше не вспоминать о них при разработке приложения для доступа к данным. Если для работы с этой же базой данных нужно создать новое приложение, триггеры и там будут отрабатывать заданные ограничения.
2. При необходимости триггерыможно изменять централизованно непосредственно в базе данных. Пользовательские программы, использующие данные из этой базы данных, не потребуют модернизации.
3. Система обработки данных, использующая триггеры, обладает лучшей переносимостью в архитектуру клиент-сервер за счет меньшего объема требуемых модификаций.
Программирование триггеров
При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект таблиц inserted и deleted, поэтому никакой другой триггер не сможет получить к ним доступ.
В зависимости от типа операции, вызвавшей выполнение триггера, содержимое таблиц inserted и deleted может быть разным:
- команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу. В таблице deleted не будет ни одной строки. После завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;
- команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить. Триггер может проверить каждую строку и определить, разрешено ли ее удаление. В таблице inserted не окажется ни одной строки;
- команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.
Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера, можно использовать функцию @@ROWCOUNT; она возвращает количество строк, обработанных последней командой. Следует помнить, что триггер запускается не при попытке изменить конкретную строку, а в момент выполнения команды изменения. Одна такая команда воздействует на множество строк, поэтому триггер должен обрабатывать все эти строки.
Если триггер обнаружил, что из 100 вставляемых, изменяемых или удаляемых строк только одна не удовлетворяет тем или иным условиям, то никакая строка не будет вставлена, изменена или удалена. Такое поведение обусловлено требованиями транзакции – должны быть выполнены либо все модификации, либо ни одной.
Триггер выполняется как неявно определенная транзакция, поэтому внутри триггера допускается применение команд управления транзакциями. В частности, при обнаружении нарушения ограничений целостности для прерывания выполнения триггера и отмены всех изменений, которые пытался выполнить пользователь, необходимо использовать команду ROLLBACK TRANSACTION. Для фиксации изменений, внесенных при выполнении транзакции, следует использовать команду COMMIT TRANSACTION.
Для удаления триггера используется команда
DROP TRIGGER{имя_триггера} [,...n].Примеры использования триггеров
Пример 1. Реализовать ограничение на значение.
При добавлении записи в таблицу Students автоматически проверяется количество студентов в заданной группе и, если их количество больше 20, то происходит откат транзакции. Если же студентов в данной группе меньше 20, то происходит увеличение количества студентов на 1 и происходит добавление записи в таблицу Students.
Команда вставки записи в таблицу Students может быть такой:
INSERT INTO Students (FIO, Nomer_zachetki, ID_Group, Stipendiya) VALUES ('Ильин С.В.', '08ВП131', 2, 1250);