Создание и использование триггеров

Триггер – это специальный класс хранимой процедуры. В этой лекции вы узнаете, что выполняют триггеры и когда их следует использовать. Вы также узнаете о расширении возможностей триггеров в Microsoft SQL Server 2000. Вы изучите на практике два метода создания триггеров: с помощью операторов Transact-SQL (T-SQL) и с помощью SQL Server Enterprise Manager. Вы также узнаете, как управлять триггерами и модифицировать их.

Что такое триггер?

Триггер – это специальный тип хранимой процедуры, которая запускается автоматически системой SQL Server при модифицировании какой-либо таблицы одним из трех операторов: UPDATE, INSERT или DELETE. Триггеры, как другие хранимые процедуры, могут содержать простые или сложные операторы T-SQL. В отличие от других типов хранимых процедур триггеры запускаются автоматически при указанных модификациях данных; их нельзя запустить вручную по имени. Когда происходит запуск триггера, говорят, что он активизируется (fire). Триггер создается по одной таблице базы данных, но он может осуществлять доступ и к другим таблицам и объектам других баз данных. Триггеры нельзя создать по временным таблицам или системным таблицам, а только по определенным пользователем таблицам или представлениям. Таблица, по которой определяется триггер, называется таблицей триггера.

Существует пять типов триггеров: UPDATE, INSERT, DELETE, INSTEAD OF и AFTER. Как следует из названий, триггер UPDATE активизируется, когда выполняются изменения (обновления) в какой-либо таблице, триггер INSERT активизируется, когда происходит вставка данных в таблицу и триггер DELETE активизируется, когда из таблицы удаляются данные. Триггер INSTEAD OF выполняется вместо операции вставки, обновления или удаления. Триггер AFTER активизируется после какой-либо запускающей операции и обеспечивает механизм управления порядком выполнения нескольких триггеров.

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

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

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

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

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

Если вам нужно назначить переменную внутри триггера, используйте оператор SET NOCOUNT ON в начале триггера, чтобы не было возвращаемых результирующих строк. Оператор SET NOCOUNT указывает, нужно ли возвращать сообщение, указывающее, сколько строк было затронуто запросом или оператором (например, "23 rows affected"). По умолчанию для SET NOCOUNT задается значение OFF, а это означает выдачу сообщения о количестве затронутых строк. Отметим, что этот параметр не влияет на реальные возвращаемые результаты из оператора SELECT ; он влияет только на возврат сообщений о количестве строк.

Расширение возможностей триггеров в SQL Server 2000

В SQL Server 2000 включены два новых триггера: триггер INSTEAD OF и триггер AFTER. Триггер INSTEAD OF выполняется вместо запуска оператора SQL. Тем самым переопределяется действие запускающего оператора. Вы можете задать по одному триггеру INSTEAD OF на один оператор INSERT, UPDATE или DELETE. Триггер INSTEAD OF можно задать для таблицы и/или представления. Вы можете использовать каскады триггеров INSTEAD OF, определяя представления поверх представлений, где каждое представление имеет отдельный триггер INSTEAD OF. Триггеры INSTEAD OF не разрешается применять для модифицируемых представлений, содержащих опцию WITH CHECK. Прежде чем задавать триггер INSTEAD OF для одного из этих представлений, вы должны удалить опцию WITH CHECK из модифицируемого представления с помощью команды ALTER VIEW. Более подробную информацию по созданию представлений см. в "Создание и использование представлений" .

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

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