Какие типы триггеров существуют в sql server

Триггеры DML

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

Преимущества триггеров DML

Триггеры DML аналогичны ограничениям в том, что могут предписывать целостность сущностей или целостность домена. Вообще говоря, целостность сущностей должна всегда предписываться на самом нижнем уровне с помощью индексов, являющихся частью ограничений PRIMARY KEY и UNIQUE или создаваемых независимо от ограничений. Целостность домена должна быть предписана через ограничения CHECK, а ссылочная целостность — через ограничения FOREIGN KEY. Триггеры DML наиболее полезны в тех случаях, когда функции ограничений не удовлетворяют функциональным потребностям приложения.

В следующем списке приведено сравнение триггеров DML с ограничениями и указано, чем триггеры DML лучше ограничений.

Триггеры DML позволяют каскадно проводить изменения через связанные таблицы в базе данных; но эти изменения могут осуществляться более эффективно с использованием каскадных ограничений ссылочной целостности. Ограничения FOREIGN KEY могут проверить значения столбца только на предмет точного совпадения со значениями другого столбца, за исключением случаев, когда с помощью предложения REFERENCES задаются каскадные ссылочные действия.
Для предотвращения случайных или неверных операций INSERT, UPDATE и DELETE и реализации других более сложных ограничений, чем те, которые определены при помощи ограничения CHECK. В отличие от ограничений CHECK, DML-триггеры могут ссылаться на столбцы других таблиц. Например, триггер может использовать инструкцию SELECT для сравнения вставленных или обновленных данных и выполнения других действий, например изменения данных или отображения пользовательского сообщения об ошибке.
Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия.
Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.
Ограничения могут сообщать об ошибках только с помощью соответствующих стандартных системных сообщений. Если для пользовательского приложения требуются более сложные методы управления ошибками и, соответственно, пользовательские сообщения, то необходимо использовать триггер.
При использовании триггеров DML может произойти откат изменений, нарушающих ссылочную целостность, что приводит к запрету модификации данных. Подобные триггеры могут применяться при изменении внешнего ключа в случаях, когда новое значение не соответствует первичному ключу. Обычно в указанных случаях используются ограничения FOREIGN KEY.
Если в таблице триггеров существуют ограничения, то их проверка осуществляется между выполнением триггеров INSTEAD OF и AFTER. В случае нарушения ограничений выполняется откат действий триггеров INSTEAD OF, а триггер AFTER не срабатывает.

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

Триггер AFTER
Триггеры AFTER выполняются после выполнения действий инструкции INSERT, UPDATE, MERGE или DELETE. Триггеры AFTER никогда не выполняются, если происходит нарушение ограничения, поэтому эти триггеры нельзя использовать для какой-либо обработки, которая могла бы предотвратить нарушение ограничения. Для каждой из операций INSERT, UPDATE или DELETE в указанной инструкции MERGE соответствующий триггер вызывается для каждой операции DML.

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

Функциональность триггеров AFTER и INSTEAD OF сравнивается в следующей таблице.

Декларативные ссылочные действия.

Создание таблицinserted и deleted .

Вместо: действие, запускающее триггер

Триггеры CLR
Триггер CLR может быть либо триггером AFTER, либо триггером INSTEAD OF. Триггер CLR может также являться триггером DDL. Вместо выполнения хранимой процедуры Transact-SQL триггер CLR выполняет один или несколько методов, написанных в управляемом коде, которые являются членами сборки, созданной в .NET Framework и переданной в SQL Server.

Связанные задачи

Задача	Раздел
Описывает, как создать триггер DML.	Создание триггеров DML
Описывает, как создать триггер CLR.	Создание триггеров CLR
Описывает, как создать триггер DML для выполнения и однострочных, и многострочных операций модификации данных.	Создание триггеров DML для обработки нескольких строк данных
Описывает, как вкладывать триггеры.	Создание вложенных триггеров
Описывает, как указывать порядок, в котором активируются триггеры AFTER.	Указание первого и последнего триггеров
Описывает, как использовать специальные таблицы inserted и deleted в коде триггера.	Использование вставленных и удаленных таблиц
Описывает, как изменить или переименовать триггер DML.	Изменение или переименование триггеров DML
Описывает, как просматривать сведения о триггерах DML.	Получение сведений о триггерах DML
Описывает, как удалять или отключать триггеры DML.	Удаление или отключение триггеров DML
Описывает, как управлять безопасностью триггеров.	Управление безопасностью триггеров

Триггеры в MS SQL Server

• Триггер – это откомпилированная SQLпроцедура
• Исполнение обусловлено наступлением
определенных событий внутри
реляционной базы данных
• Не имеет параметров
• Становится «одним целым» с вызвавшей
операцией

3. Виды триггеров

Триггеры
DML-триггеры
DDL-триггеры
DML-события:
Insert,
Delete,
Update
DDL-события:
Create,
Drop,
Alter
Logon-триггеры
Logon
Появились в
SQL Server
2005

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

• Проверка корректности введенных данных и выполнение
сложных ограничений целостности данных, которые трудно, если
вообще возможно, поддерживать с помощью ограничений
целостности, установленных для таблицы.
• Накопление аудиторской информации посредством фиксации сведений
о внесенных изменениях и тех лицах, которые их выполнили.
• Автоматического оповещения других модулей о том, что делать в
случае изменения информации содержащейся в таблице БД.
• Для реализации так называемых «бизнес правил».
• Для организации каскадных воздействий на таблицы БД (могут
действовать не только при равенстве значений).
• Поддержка репликации.

5. Когда нужны триггеры

• Чтобы оценить состояние таблицы до и после
изменения данных и предпринять действия на основе
этого различия.
• Для предотвращения действий, нарушающих бизнеслогику приложения
• Несколько DML-триггеров одинакового типа (INSERT,
UPDATE или DELETE) для таблицы позволяют
предпринять несколько различных действий в ответ
на одну инструкцию изменения данных.

6. DML-триггеры

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

7. DML — trigger

• Объект — таблица, VIEW
• Событие — insert, update, delete для
таблицы и для VIEW.
• Время активации – до (вместо) или
после выполнения оператора.

8. DML-триггеры

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

9. Когда нужны триггеры

• Для каскадных изменений в связанных таблицах БД
(если их нельзя выполнить при помощи каскадных
ограничений ссылочной целостности).
• Для предотвращения случайных или неправильных
операций INSERT, UPDATE и DELETE
• Для реализации ограничений целостности, которые
нельзя определить при помощи ограничения
CHECK. DML-триггеры могут ссылаться на столбцы
других таблиц.

10. Еще…

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

11. Когда не надо использовать триггеры

• Не нужно реализовывать триггерами
возможности, достигаемые использованием
декларативных средств СУБД (ограничения
целостности или внешние ключи)
• Избегайте сложных цепочек триггеров

12. Советы

• Не используйте триггеры, если можно
применить проверочное
ограничение CHECK
• Не используйте ограничение CHECK,
если можно обойтись
ограничением UNIQUE.

13. Основные параметры триггера

• Имя триггера
• Имя таблицы (или представления)
• Время срабатывания:
AFTER(FOR) или INSTEAD OF
• Событие: INSERT, UPDATE, DELETE (TRUNCATE TABLE
– это не удаление !)
• Тело триггера
!
Последовательность срабатывания однотипных триггеров
произвольна

14. Группировка событий

• Например, вы можете создать триггер,
который будет активизироваться, когда
происходит выполнение
оператора UPDATE или INSERT, и такой триггер
мы будем называть триггером UPDATE/INSERT.
Вы можете даже создать триггер, который
будет активизироваться при возникновении
любого из трех событий модификации
данных (триггер UPDATE/INSERT/DELETE).

15. Правила работы триггера

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

16. Правила работы триггера

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

17. Пример

CREATE TRIGGER trg ON my_table
FOR INSERT, UPDATE, DELETE AS
select «this is trigger»

18. Рекурсия

• Косвенная рекурсия
При косвенной рекурсии приложение обновляет
таблицу T1. Это событие вызывает срабатывание
триггера TR1, обновляющего таблицу T2. Это вызывает
срабатывание триггера T2 и обновление таблицы T1.
• Прямая рекурсия
При прямой рекурсии приложение обновляет таблицу
T1. Это событие вызывает срабатывание триггера TR1,
обновляющего таблицу T1. Поскольку таблица T1 уже
была обновлена, триггер TR1 срабатывает снова и т. д.

19.

• При вызове триггера будут выполнены
операторы SQL, указанные после ключевого
слова AS. Вы можете поместить сюда
несколько операторов, включая
программные конструкции, такие
как IF и WHILE.

20. Выбор типа триггера

• Триггеры INSTEAD OF используются для:
– Выборочного запрещения исполнения команды,
для которой определен триггер (проверки предусловия);
– Подсчета значений столбцов до завершения
команды INSERT или UPDATE.
• Триггеры AFTER используются для:
– Учета выполненных операций;
– Проверки пост-условий исполнения команды.

21. Циклы и вложенность

• SQL Server позволяет использовать вложенные
триггеры, до 32 уровней вложенности. Если
любой из вложенных триггеров выполняет
операцию ROLLBACK, то последующие триггеры
не запускаются.
• Запуск триггеров отменяется, если
формируется бесконечный цикл.

22. Триггер INSTEAD OF

Триггер INSTEAD OF
• Триггер INSTEAD OF выполняется вместо запуска оператора
SQL. Тем самым переопределяется действие запускающего
оператора.
• Можно задать по одному триггеру INSTEAD OF на один
оператор INSERT, UPDATE или DELETE.
• Триггер INSTEAD OF можно задать для таблицы и/или
представления
• Можно использовать каскады триггеров INSTEAD OF,
определяя представления поверх представлений, где каждое
представление имеет отдельный триггер INSTEAD OF.
• Триггеры INSTEAD OF не разрешается применять для
модифицируемых представлений, содержащих опцию WITH
CHECK.

23. Триггер AFTER

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

24. Триггер AFTER

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

25. Порядок AFTER-триггеров

• sp_settriggerorder @triggername =
‘AnotherTrigger’, @order = ‘first’
• sp_settriggerorder @triggername =
‘MyTrigger’, @order = ‘last’
• sp_settriggerorder @triggername =
‘MyOtherTrigger’, @order = ‘none’
• sp_settriggerorder @triggername =
‘YetAnotherTrigger’, @order = ‘none’

26. Использование таблиц deleted и inserted

• При создании триггера вы имеете доступ к
двум временным таблицам с именами
deleted и inserted. Они хранятся в памяти, а
не на диске.
• Эти две таблицы имеют одинаковую
структуру с таблицей (одинаковые колонки
и типы данных), по которой определяется
данный триггер.

27. Использование inserted, deleted

Специальные таблицы:
• inserted – вставленные значения (для INSERT,
UPDATE)
• deleted – удаленные значения (для UPDATE,
DELETE)

28. Использование таблиц deleted и inserted

• Таблица deleted содержит копии строк, на которые
повлиял оператор DELETE или UPDATE. Строки,
удаляемые из таблицы данного триггера,
перемещаются в таблицу deleted. После этого к
данным таблицы deleted можно осуществлять доступ
из данного триггера.
• Таблица inserted содержит копии строк, добавленных
к таблице данного триггера при выполнении
оператора INSERT или UPDATE. Эти строки
добавляются одновременно в таблицу триггера и в
таблицу inserted.

29. Использование таблиц deleted и inserted

• Поскольку оператор UPDATE обрабатывается
как DELETE, после которого следует INSERT, то
при использовании оператора UPDATE старые
значения строк копируются в таблицу deleted,
а новые значения строк – в таблицу триггера и
в таблицу inserted.
• Триггер INSERT => deleted пуст
• Триггер DELETE => inserted пуст
• но сообщение об ошибке не возникнет !

30. 31. Создание триггера

CREATE TRIGGER [ schema_name.]trigger_name ON
< table | view >
< FOR | AFTER | INSTEAD OF >
< [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >
AS < sql_statement>

32.

CREATE TRIGGER plus_1
ON table1
instead of insert
AS
insert table1 (id, col1) select id+1, col1
from inserted;

33.

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

34. Обработка исключений

Команда ROLLBACK указывает серверу остановить обработку
модификации и запретить транзакцию.
Существует также команда RAISEERROR, с помощью которой вы
можете отправить сообщение об ошибке пользователю.
TRY…CATCH

35. Обработка исключений сообщение об ошибке

RAISERROR (‘Error raised because of wrong data.’, — Message text.
16, — Severity.
1 — State.);
Severity – число от 0 до 25
Определенный пользователем уровень серьезности ошибки.
0 до 18 может указать любой пользователь.
19 до 25 могут быть указаны только sysadmin
20 до 25 считаются неустранимыми — соединение с клиентом
обрывается и регистрируется сообщение об ошибке в журналах
приложений и ошибок.
State Целое число от 0 до 255. Отрицательные значения или значения
больше 255 приводят к формированию ошибки. Если одна и та же
пользовательская ошибка возникает в нескольких местах, то при
помощи уникального номера состояния для каждого местоположения
можно определить, в каком месте кода появилась ошибка.

36. Функции об ошибках

• Функция ERROR_LINE() возвращает номер строки, в которой
произошла ошибка.
• Функция ERROR_MESSAGE() возвращает текст сообщения,
которое будет возвращено приложению. Текст содержит
значения таких подставляемых параметров, как длина,
имена объектов или время.
• ERROR_NUMBER() возвращает номер ошибки.
• Функция ERROR_PROCEDURE() возвращает имя хранимой
процедуры или триггера, в котором произошла ошибка. Эта
функция возвращает значение NULL, если данная ошибка не
была совершена внутри хранимой процедуры или триггера.
• ERROR_SEVERITY() возвращает уровень серьезности ошибки.
• ERROR_STATE() возвращает состояние.

37. Пример триггера

CREATE TRIGGER LowCredit ON
Purchasing.PurchaseOrderHeader AFTER INSERT AS
BEGIN
DECLARE @creditrating tinyint, @vendorid int ;
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted i ON p.PurchaseOrderID = i.PurchaseOrderID JOIN
Purchasing.Vendor v ON v.VendorID = i.VendorID ;
IF @creditrating = 5
RAISERROR (‘This vendor»s credit rating is too low to accept new purchase
orders.’, 16, 1) ;
END

38. Управление триггерами

• Отключение/включение триггера:
– DISABLE/ENABLE TRIGGER trigger_name ON
object_name
• Отключение/включение всех триггеров
таблицы:
– DISABLE/ENABLE TRIGGER ALL ON object_name
• Изменение триггера:
– ALTER TRIGGER trigger_name …
• Удаление триггера:
– DROP TRIGGER trigger_name

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

ALTER TRIGGER tr_name
ON on_board
after UPDATE
AS
update on_board set iks=’b’ where id in (select
id from inserted)

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

• DROP TRIGGER tr_name

41. Активация/деактивация триггера

• DISABLE TRIGGER ON < object_name>;
• ENABLE TRIGGER ON < object_name>

42. Применение триггеров

• Защита
– Запрещение доступа в зависимости от значений
данных
• Учет
– Ведение журналов изменений
• Целостность данных
– Сложные правила целостности
– Сложная ссылочная целостность
• Производные данные
– автоматическое вычисление значений

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

Функция
Триггер AFTER
Триггер INSTEAD OF
Сущности
Таблицы
Таблицы и представления
Количество триггеров на
таблицу/представление
Несколько на одно событие
Один триггер на одно событие
Нет ограничений
INSTEAD OF UPDATE и DELETE
нельзя определять для таблиц, на
которые распространяются каскадные
ограничения ссылочной целостности.
Каскадные ссылки
После следующих операций:
Обработка ограничений.
Выполнение
Декларативные ссылочные
действия.
Создание таблиц inserted и
deleted.
Действие, запускающее
триггер.
Перед следующей операцией:
Обработка ограничений.
Вместо следующей операции:
Действие, запускающее триггер.
После следующих операций:
Создание таблиц inserted и
deleted.

44. DDL — trigger

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

45. DDL — триггеры

• Триггеры DDL, как и обычные триггеры,
вызывают срабатывание хранимых процедур
в ответ на событие.
• Срабатывают в ответ на разнообразные
события языка определения данных (DDL).
• Эти события в основном соответствуют
инструкциям языка Transact-SQL,
начинающимся ключевыми словами CREATE,
ALTER или DROP.

46. Задачи для DDL — триггеров

• Предотвратить внесение определенных
изменений в схему базы данных.
• Выполнить в базе данных некоторые действия в
ответ на изменения в схеме базы данных.
• Записывать изменения или события схемы базы
данных.
• Триггеры DDL срабатывают только после
выполнения соответствующих инструкций DDL.
Триггеры DDL нельзя использовать в качестве
триггеров INSTEAD OF.

47.

CREATE TRIGGER trigger_name
ON < DATABASE | ALL SERVER >
< FOR | AFTER >< event_type | event_group >
AS
< sql_statement [ ; ] [ . n ] [ ; ] >

48. Создание/удаление DDL-тр

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT ‘Database Created.’
DROP TRIGGER ddl_trig_database
ON ALL SERVER;

49. DDL — trigger

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE AS
PRINT ‘You must disable Trigger «safety» to
drop or alter tables!’
ROLLBACK ;

50.

• Для одной инструкции Transact-SQL можно создать
несколько триггеров DDL.
• Триггер DDL и инструкция, приводящая к его
срабатыванию, выполняются в одной транзакции.
• Откат событий ALTER DATABASE, возникших внутри
триггера DDL, невозможен.
• Триггеры DDL выполняются только после завершения
инструкции Transact-SQL. Триггеры DDL нельзя
использовать в качестве триггеров INSTEAD OF.
• Триггеры DDL не создают таблицы inserted и deleted.

51. Logon — trigger

• Триггеры входа выполняют хранимые
процедуры в ответ на событие LOGON. Это
событие вызывается при установке
пользовательского сеанса с экземпляром SQL
Server.
• Триггеры входа срабатывают после
завершения этапа проверки подлинности при
входе, но перед тем, как пользовательский
сеанс реально устанавливается.

3.4. Триггеры

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

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

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

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

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

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

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

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

Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

Листинг 3.2. Общий вид команды CREATE TRIGGER

CREATE TRIGGER trigger_name ON < table | view >[ WITH ENCRYPTION ] < < < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] >[ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ < IF UPDATE ( column ) [ < AND | OR >UPDATE ( column ) ] [ . n ] | IF (COLUMNS_UPDATED() updated_bitmask) < comparison_operator >column_bitmask [ . n ] > ] sql_statement [ . n ] > >

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

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

ALTER DATABASE;
CREATE DATABASE;
DISK INIT;
DISK RESIZE;
DROP DATABASE;
LOAD DATABASE;
LOAD LOG;
RECONFIGURE;
RESTORE DATABASE;
RESTORE LOG.

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

3.4.2. Откат изменений в триггере

Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера — откатываются.

При использовании отката изменений, вы должны учитывать следующее:

Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS ROLLBACK TRANSACTION

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

имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
после подчеркивания идет имя таблицы, для которого создается триггер.

После имени идет ключевое слово ON и имя таблицы, для которой создается триггер.

Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда — ROLLBACK TRANSACTION, т.е. откат.

Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:

UPDATE tbPeoples SET vcFamil='dsfg'

В данном примере мы пытаемся изменить содержимое поля «vcFamil» для всех записей таблицы tbPeoples. Почему пытаемся? Да потому что при изменении срабатывает триггер с откатом транзакции. Выполните выборку данных, чтобы убедиться, что все данные на месте и не изменились:

SELECT * FROM tbPeoples

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

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

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

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

Листинг 3.3. Оператор обновления триггера

ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] < < ( FOR | AFTER | INSTEAD OF ) < [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] >[ NOT FOR REPLICATION ] AS sql_statement [ . n ] > | < ( FOR | AFTER | INSTEAD OF ) < [ INSERT ] [ , ] [ UPDATE ] >[ NOT FOR REPLICATION ] AS < IF UPDATE ( column ) [ < AND | OR >UPDATE ( column ) ] [ . n ] |IF(COLUMNS_UPDATED() < bitwise_operator >updated_bitmask) < comparison_operator >column_bitmask [ . n ] > sql_statement [ . n ] > >

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

ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE, INSERT AS ROLLBACK TRANSACTION

Как видите, оператор обновления похож на создание триггера. Разница в том, что в первой строке стоит оператор ALTER TRIGGER. Во второй строке произошло изменение, и теперь триггер будет срабатывать не только на обновление (UPDATE), но и на добавление (INSERT).

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

INSERT INTO tbPeoples(vcFamil) VALUES('ПЕТЕЧКИН')

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

ALTER TABLE table TRIGGER

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

ALTER TABLE tbPeoples DISABLE TRIGGER u_tbPeoples

В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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

ALTER TABLE tbPeoples ENABLE TRIGGER ALL

3.4.4. Удаление триггеров

Для удаления триггера вы можете воспользоваться оператором DROP TRIGGER. Он удаляется автоматически, когда связанная с ним таблица удаляется.

Пример удаления триггера:

DROP TRIGGER u_tbPeoples

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

3.4.5. Как работают триггеры?

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

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

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

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

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

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

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

Листинг 3.4. Использование таблицы inserted

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS DECLARE @Name varchar(50) SELECT @Name=vcName FROM inserted IF @Name='ВАСЯ' BEGIN PRINT 'ОШИБКА' ROLLBACK TRANSACTION END

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля «vcName» таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля «vcName». Код такого триггера можно увидеть в листинге 3.5.

Листинг 3.5. Запрет нулевых значений в поле с помощью триггера

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS IF EXISTS (SELECT * FROM inserted WHERE vcName is NULL) BEGIN PRINT 'ОШИБКА, вы должны заполнить поле vcName' ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля «vcName», то откатываем попытку добавления.

Триггер DELETE

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

Удаляемые строки помещаются в таблицу deleted, с помощью которой вы можете увидеть удаляемые строки. Это логическая таблицf, которая ссылается на данные журнала оператора DELETE.

Вы должны учитывать:

когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
для таблицы deleted выделяется память, поэтому она всегда в кэше;
триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

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

Листинг 3.6. Пример запрета удаления с помощью триггера

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE vcName='рлр') BEGIN PRINT 'ОШИБКА, нельзя удалить этого пользователя' ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице deleted существует запись с именем «рлр», то откатываем удаление. Добавьте в таблице запись с именем «рлр» и попытайтесь ее удалить. В ответ вы должны увидеть ошибку.

А что если попытаться удалить несколько записей? Например, в следующем примере удаляются записи две записи:

DELETE FROM tbPeoples WHERE vcName='рлр' or vcName='ВАСИЛИЙ'

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

Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE idPosition=1) BEGIN PRINT 'ОШИБКА, нельзя удалить этого пользователя' ROLLBACK TRANSACTION END

В этом примере, запрещается удаление записи, если поле «idPosition» равно 1. Попробуйте удалить такую запись:

DELETE FROM tbPeoples WHERE idPosition=1

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

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

Триггер UPDATE

Обновление происходит в два этапа – удаление и вставка. Нет, физически в базе данных происходит изменение, это триггер видит два этапа. Поэтому существующие строки помещаются в таблицу deleted (то есть то, что было), а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

Вы можете объявить триггер для мониторинга обновления определенного поля с помощью указания опции IF UPDATE. Это позволяет триггеру изолировать активность определенной колонки. Когда обнаруживается обновление определенной колонки, триггер может выполнить определенные действия, такие как выброс сообщения об ошибке, которое сообщит о невозможности обновления колонки.

Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле «vcName»

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) PRINT 'Я надеюсь, что вы правильно указали имя'

После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

Следующий запрос тестирует триггер:

UPDATE tbPeoples SET vcName='ИВАНУШКА' WHERE vcFamil='ПОЧЕЧКИН'

Убедитесь, что сообщение из триггера выводится на экран.

Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО («vcFamil», «vcName» и «vcSurName»). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) OR UPDATE (vcFamil) OR UPDATE (vcSurname) BEGIN PRINT 'Нельзя изменять фамилию, имя и отчество' ROLLBACK TRANSACTION END

С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

3.4.6. INSTEAD OF

Вы можете указать триггер INSTEAD OF для таблиц и просмотрщиков. Действия такого триггера выполняются вместо операторов, сгенерировавших триггер. Не понятно? Рассмотрим пример. Допустим, что у вас есть триггер INSTEAD OF на событие обновления таблицы. Если пользователь выполняет обновление, то выполняется триггер, но при этом, оператор, запущенный пользователем, только генерирует событие. Реальное обновление данных должно происходить с помощью операторов триггера.

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

Как можно использовать INSTEAD OF? Допустим, что у нас есть объект просмотра, который выбирает данные их двух таблиц. Как мы уже знаем, данные вьюшки можно изменять, только если все они принадлежат одной таблице. Но с помощью триггера можно сделать обновление любого количества таблиц.

Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:

CREATE VIEW People AS SELECT vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

Листинг 3.7. Триггер INSTEAD OF для вставки данных

CREATE TRIGGER i_People ON dbo.People INSTEAD OF INSERT AS BEGIN -- Добавление должности INSERT INTO tbPosition (vcPositionName) SELECT vcPositionName FROM inserted i -- Добавление работника INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName END

В этом примере интересности начинаются прямо со второй строки. Здесь указывается оператор INSTEAD OF и событие, на которое нужно реагировать. В данном случае в качестве события выступает вставка (INSERT).

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

INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName

Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:

INSERT INTO People VALUES('ИВАНУШКИН', 'Клерк')

Выполните следующий запрос и убедитесь, что новая запись добавлена:

SELECT * FROM People

При обновлении таблицы есть одна проблема – нужно связать обновляемые данные с существующими. Первым на ум приходит запрос типа:

UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.vcPositionName = pn.vcPositionName

Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:

ALTER VIEW People AS SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

Теперь INSTEAD OF триггер для обновления данных будет выглядеть, как показано в листинге 3.8.

Листинг 3.8. Обновление связанной вьюшки с помощью триггера

CREATE TRIGGER u_People ON dbo.People INSTEAD OF UPDATE AS BEGIN UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.idPosition=pn.idPosition UPDATE tbPeoples SET vcFamil=i.vcFamil FROM tbPeoples pl, inserted i WHERE i.idPeoples=pl.idPeoples END

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

UPDATE People SET vcFamil='ИВАНУШКИН', vcPositionName='Генеральный директор' WHERE idPeoples=40 AND idPosition=13

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

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

3.4.7. Дополнительно о триггерах

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

Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

При разработке триггеров, вы должны учитывать, что таблицы могут иметь несколько триггеров для любого действия. Каждый триггер может быть объявлен для нескольких или одного действия. Например, в следующем примере обрабатывается два события INSERT и UPDATE:

CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples FOR INSERT, UPDATE AS Действие

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

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

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

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

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

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

3.4.8. Практика использования триггеров

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

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

Итак, давайте создадим триггер, который при изменении или удалении строк в таблице tbPeoples будет копировать их в таблицу истории tbpeoplesHistory. Если бы первичный ключ был в виде уникального идентификатора, то задача решалась бы следующим образом:

CREATE TRIGGER ud_tbPeoples ON dbo. tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory SELECT newid(), del.* FROM Deleted del

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

В данном примере содержимое таблицы Deleted копируется в таблице tbPeoplesHistory. Запрос упрощается тем, что первичный ключ можно сгенерировать с помощью функции newid().

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

CREATE TRIGGER ud_tbPeoplesHistory ON dbo.tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) SELECT del.* FROM Deleted del

Теперь посмотрим, как можно запретить удаление более чем одной строки:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF (SELECT count(*) FROM deleted)>1 BEGIN PRINT 'Нельзя удалять более одной строки' ROLLBACK TRANSACTION END

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

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

sp_configure ‘nested triggers’, 0

Триггеры могут иметь вложения до 32 уровней. Если какой-нибудь триггер зациклится, то будет превышен предел. Триггер прерывается и транзакция откатывается.

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

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

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

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

ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON

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

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

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS DELETE pn FROM tbPhoneNumbers pn, inserted i WHERE pn.idPeoples=i.idPeoples

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

При добавлении записи в таблицу телефонов увеличиваем значение поля в таблицы работников;
При удалении номера телефона, уменьшаем значения поля.

Попробуйте реализовать это самостоятельно, чтобы закрепить знания и потренироваться в работе с SQL запросами.

Для определения таблиц с триггером, выполните процедуру sp_depends. Например, выполните следующую команду, чтобы увидеть все зависимости для таблицы tbPeoples:

EXEC sp_depends 'tbPeoples'

Для определения, какие триггеры существуют на определенную таблицу, и на какие действия выполните процедуру sp_helptrigger. Следующий пример отображает все триггеры, которые принадлежат объекту просмотра People (если нужно просмотреть триггеры таблицы, то укажите ее имя):

EXEC sp_helptrigger People

Для просмотра кода существующего триггера используйте sp_helptext. Например, следующая команда позволяет увидеть текст триггера u_People, которую мы создавали для объекта просмотра:

EXEC sp_helptext u_People

Триггеры SQL — Введение

Триггеры могут быть определены как объекты базы данных, которые выполняют некоторые действия для автоматического выполнения всякий раз, когда пользователи пытаются выполнить команды изменения данных (INSERT, DELETE и UPDATE) для указанных таблиц. Триггеры привязаны к конкретным таблицам. Согласно MSDN, триггеры могут быть определены как особый вид хранимых процедур. Эта статья даст вам подробные знания о триггерах SQL, которые могут быть очень полезны в вашей работе. Прежде чем описывать типы триггеров, мы должны сначала понять магические таблицы, на которые ссылаются триггеры и которые используются для повторного использования.

Волшебные таблицы

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

Таблица содержит все вставленные строки

Таблица не содержит строк

Таблица содержит все удаленные строки

Таблица содержит строки после обновления

Таблица содержит все строки до обновления

Разница между хранимой процедурой и триггером

Мы можем выполнить хранимую процедуру всякий раз, когда захотим, с помощью команды exec, но триггер можно выполнить только всякий раз, когда событие (вставка, удаление и обновление) запускается в таблице, для которой определен триггер.
Мы можем вызвать хранимую процедуру из другой хранимой процедуры, но мы не можем напрямую вызвать другой триггер внутри триггера. Мы можем достичь только вложенности триггеров, при которой действие (вставка, удаление и обновление), определенное внутри триггера, может инициировать выполнение другого триггера, определенного в той же или другой таблице.
Хранимые процедуры могут быть запланированы через задание для выполнения в заранее определенное время, но мы не можем запланировать триггер.
Хранимая процедура может принимать входные параметры, но мы не можем передать параметры в качестве входных данных для триггера.
Хранимые процедуры могут возвращать значения, но триггер не может возвращать значение.
Мы можем использовать команды Print внутри хранимой процедуры для отладки, но мы не можем использовать команду print внутри триггера.
Мы можем использовать операторы транзакции, такие как начало транзакции, фиксация транзакции и откат внутри хранимой процедуры, но мы не можем использовать операторы транзакции внутри триггера.
Мы можем вызвать хранимую процедуру из внешнего интерфейса (.asp-файлы, .aspx-файлы, .ascx-файлы и т. Д.), Но мы не можем вызвать триггер из этих файлов.

Триггеры DML

Типы триггера

В SQL Server есть два типа триггеров, которые приведены ниже:

Триггеры AFTER
Триггеры INSTEAD OF

В этой статье мы будем использовать три таблицы с именами customer, customerTransaction и Custmail, структура которых приведена ниже:

Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)
Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)
Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))

Триггеры AFTER

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

Синтаксис триггера AFTER

Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
Begin
//SQL Statements
End

Пример триггера AFTER для вставки

Предположим, у нас есть требование, что всякий раз, когда добавляется новый клиент, автоматически его соответствующее значение должно быть вставлено в таблицу Custmail, чтобы можно было отправить электронное письмо клиенту и уполномоченному лицу в Банке. Чтобы решить эту проблему, мы можем создать триггер After Insert для таблицы customer, синтаксис которой приведен ниже:

Create Trigger trig_custadd on Customer
For Insert
As
Begin
Declare @Custnumber as nvarchar( 100 )
Declare @amount as int
Declare @custid as int
Select @Custnumber=Custnumber, @amount=Amount From inserted
Select @custid=customerid From customer Where Custnumber =@Custnumber
Insert Into Custmail (custid,Amt,Mailreason)
Values (@custid,@amount, ‘New Customer’ )
End

Этот триггер сработает всякий раз, когда новый клиент добавляется в банк и соответствующая запись вставляется в таблицу Custmail. Функциональность почты будет использовать записи из таблицы custmail для отправки почты клиенту.

Пример триггера AFTER для удаления

Предположим, есть еще одно требование, что всякий раз, когда клиент удаляется из системы, ему отправляется почта, содержащая уведомление об удалении. Для отправки почты нам необходимо вставить запись клиента в таблицу custmail всякий раз, когда клиент удаляется из главной таблицы клиентов. Для этого мы будем использовать триггер AFTER для удаления. В приведенном ниже примере мы будем использовать волшебную таблицу Deleted.

Create trigger trig_custdelete on customer
For Delete
As begin
Declare @Custnumber as nvarchar( 100 )
Declare @custid as int
Select @Custnumber=Custnumber from deleted
Select @custid=customerid from customer where Custnumber =@Custnumber
Delete from customerTransaction where custid=@custid
Insert into Custmail
Values(@custid, 0 , ‘Customer delete’ )
end

Пример триггера AFTER для обновления

Предположим, у нас также есть требование, чтобы всякий раз, когда клиент зачислял средства на свою учетную запись или обновлял свое имя (имя и фамилию), клиенту должно быть отправлено письмо, содержащее эту информацию. В этом случае мы можем использовать триггер After для обновления. В этом примере мы будем использовать Магическую таблицу Inserted.

create trigger trig_Custupdate
on customer
for update
as
begin
declare @Custnumber as nvarchar( 100 )
declare @amount as int
Declare @custid as int
if update(amount)
begin
select @Custnumber=Custnumber, @amount=Amount from inserted
select @custid=customerid from customer where Custnumber =@Custnumber
insert into Custmail
values(@custid,@amount, ‘Customer Amount Update’ )
end
if update(custFname)or update(CustEnamn)
begin
insert into Custmail
values(@custid, 0 , ‘Customer Name Update’ )
end
end

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

Триггеры INSTEAD OF

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

могут быть определены в случае вставки, удаления и обновления. Например, предположим, что у нас есть условие, что в одной транзакции пользователь не сможет дебетовать более 15000 долларов. Мы можем использовать триггер вместо, чтобы реализовать это ограничение. Если пользователь пытается снять со своего счета более 15000 долларов за один раз, появляется сообщение об ошибке « Cannot Withdraw more than 15000 at a time ». В этом примере мы используем волшебную таблицу Inserted.

Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
declare @Custnumber as nvarchar( 100 )
declare @amount as int
Declare @custid as int
Declare @mode as nvarchar( 10 )
select @custid =custid , @amount=Transactionamt,@mode=mode from
inserted
if @mode = ‘c’
begin
update customer set amount=amount+ @amount where
customerid= @custid
insert into Custmail
values( @custid ,@amount, ‘Customer Amount Update’ )
end
if @mode = ‘d’
begin
if @amount < = 15000
begin
update customer set amount=amount- @amount where
customerid= @custid
insert into Custmail
values( @custid ,@amount, ‘Customer Amount Update’ )
end
else
begin
Raiserror (‘Cannot Withdraw more than 15000 at a time’,16,1)
rollback;
end
end
end

Триггеры DDL

У триггеров DDL такое же поведение, как и у триггеров DML, за исключением того, что они запускаются в ответ на событие типа DDL, такое как команда Alter, команда Drop и команды Create. Другими словами, он будет срабатывать в ответ на события, которые пытаются изменить схему базы данных. Поэтому эти триггеры не создаются для конкретной таблицы, но они применимы ко всем таблицам в базе данных. Также триггеры DDL могут быть запущены только после выполнения команд, которые их запускают. Они могут быть использованы для следующих целей:

1) Чтобы предотвратить любые изменения в схеме базы данных

2) Если мы хотим хранить записи всех событий, которые меняют схему базы данных.

Например, предположим, что мы хотим создать таблицу command_log, в которой будут храниться все пользовательские команды для создания таблиц (Create table) и команды, которые изменяют таблицы. Также мы не хотим, чтобы какая-либо таблица был удалена. Поэтому, если какая-либо команда удаления таблицы запущена, триггер DDL откатит команду с сообщением «Вы не можете удалить таблицу».

Скрипт для таблицы command_log будет приведен ниже:

CREATE TABLE Command_log(id INT identity( 1 , 1 ), Commandtext NVARCHAR( 1000 ), Commandpurpose nvarchar( 50 ))

DDL Trigger для создания таблицы

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

CREATE TRIGGER DDL_Createtable
ON database
FOR CREATE_Table
AS
Begin
PRINT ‘Table has been successfully created.’
insert into command_log ()
Select EVENTDATA(). value ( ‘(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ‘ , ‘nvarchar(1000)’ )

End

Этот триггер срабатывает всякий раз, когда запускается любая команда для создания таблицы, и вставляет команду в таблицу command_log, а также выводит сообщение «Таблица была успешно создана».

Примечание. Eventdata () — это функция, которая возвращает информацию о событиях сервера или базы данных. Возвращает значение типа XML.

DDL Trigger для изменения таблицы

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

Create Trigger DDL_Altertable
On Database
for Alter_table
as
begin
declare @coomand as nvarchar(max)
print ‘Table has been altered successfully’
insert into command_log(commandtext)
Select EVENTDATA(). value ( ‘(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]’ , ‘nvarchar(1000)’ )

end

Этот триггер срабатывает всякий раз, когда в базе данных запускается любая команда alter table, и выводит сообщение «Таблица успешно изменена».

DDL Trigger для удаления таблицы

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

Create TRIGGER DDL_DropTable
ON database
FOR Drop_table
AS
Begin
PRINT ‘Table cannot be dropped.’
INSERT into command_log(commandtext)
Select EVENTDATA(). value ( ‘(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]’ , ‘nvarchar(1000)’ )
Rollback;
end

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

Вложенные триггеры

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

Например, предположим, что существует триггер t1, определенный в таблице tbl1, и есть другой триггер t2, определенный в таблице tbl2, если действие триггера t1 инициирует триггер t2, то оба триггера называются вложенными. В SQL Server триггеры могут быть вложены до 32 уровней. Если действие вложенных триггеров приводит к бесконечному циклу, то после 32 уровня триггер завершается.

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

Мы также можем остановить выполнение вложенных триггеров с помощью следующей команды SQL:

Рекурсивные триггеры

В SQL Server у нас могут быть рекурсивные триггеры, где действие триггера может инициироваться снова. В SQL Server у нас есть два типа рекурсии:

Прямая рекурсия
Непрямая рекурсия

В прямой рекурсии действие триггера снова инициирует сам триггер, что приводит к рекурсивному вызову триггера.

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

Обратите внимание: рекурсивный триггер возможен только при установленной опции рекурсивного триггера.

Опцию рекурсивного запуска можно установить с помощью следующей команды SQL:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Как найти триггеры в базе данных

1. Нахождение всех триггеров, определенных для всей базы данных

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

select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on o1.parent_object_id=o2.object_id and o1.type_desc= ‘sql_trigger’

2. Нахождение всех триггеров, определенных в конкретной таблице

Например, если мы хотим выяснить все триггеры, созданные в таблице Customer, мы можем использовать следующую инструкцию SQL:

sp_helptrigger Tablename
example:-
sp_helptrigger ‘Customer’

3. Нахождение определения триггера

Предположим, что если мы хотим узнать определение триггера, мы можем использовать следующую инструкцию SQL:

sp_helptext triggername
For example:-
sp_helptext ‘trig_custadd’

Результат:

Как отключить триггер

Отключение триггера DML для таблицы

DISABLE TRIGGER ‘trig_custadd’ ON Customer;

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

DISABLE TRIGGER ‘DDL_Createtable’ ON DATABASE;

Отключение всех триггеров, которые были определены с одинаковой областью действия

DISABLE Trigger ALL ON ALL SERVER;

Как включить триггер

Включение триггера DML для таблицы

ENABLE Trigger ‘trig_custadd’ ON Customer;

Включение триггера DDL

ENABLE TRIGGER ‘DDL_Createtable’ ON DATABASE;

Включение всех триггеров, которые были определены с одинаковой областью действия

ENABLE Trigger ALL ON ALL SERVER;

Как сбросить триггер

Сбрасывание триггера DML:

DROP TRIGGER trig_custadd ;

Сбрасывание триггера DDL:

DROP TRIGGER DDL_Createtable ON DATABASE

Пример из реальной жизни

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

Пользователь добавлен в систему.
Всякая информация, касающаяся пользователя, обновляется, удаляется или добавляется.
Пользователь удален.

Проблемы в этой задаче включают в себя:

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

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

Возможные решения:

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

Два подхода для вставки строк:

Найдите все места в файлах .aspx и хранимых процедурах, где есть запросы на вставку, удаление и обновление, и после этих запросов добавляют запрос на вставку для таблицы tblmail.
Вместо того, чтобы искать эти запросы во всех файлах и хранимых процедурах .axps, создайте триггер after (вставка, обновление и удаление) в основной таблице пользователя, который вставит дату в таблицу tblmail после выполнения оператора вставки, обновления и удаления.

Мы использовали второй подход по следующим 4 причинам:

1) Очень сложно найти столько файлов .aspx и хранимых процедур, чтобы найти требуемые запросы.

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

3) Если нам нужно что-то изменить в требовании, оно должно быть изменено во всех этих файлах и хранимых процедурах.

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

Преимущества триггеров SQL

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

2) Иногда они помогают сохранить короткие и простые коды SQL, как показано на примере из реальной жизни.

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

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

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

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

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

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

2) Их трудно отлаживать, так как их трудно просматривать по сравнению с хранимыми процедурами, представлениями, функциями и т. д.

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

4) Если в триггерах написан сложный код, это замедлит работу приложений.

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

Резюме

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