DDL-триггер и переименование объекта

Источник: blogstechnet

1. Как известно, в SQL Server 2005 появилась возможность навешивать триггера не только на операции изменения данных (DML), но и на декларативные (DDL) операции - CREATE / ALTER / DROP всевозможных таблиц, представлений, процедур, пользователей и пр.объектов как уровня базы, так и уровня сервера. Полный список событий, для которых можно создать DDL-триггер, находится здесь: http://msdn.microsoft.com/ru-ru/library/bb522542.aspx. Аналогом служебных таблиц inserted/deleted для DDL-триггера является функция EVENTDATA(), выдающая служебную информацию о том, кто, где, когда, что сделал. Для удобства события объединяются в группы событий, чтобы муторно не перечислять при создании, что этот триггер должен вспыхивать, например, на create table, alter table, drop table. Группы событий перечислены здесь - http://msdn.microsoft.com/ru-ru/library/bb510452.aspx. Поэтому если вы не знаете, как в точности называется событие, на которое вы хотите повесить триггер, вешайте на общую группу и смотрите в результатах EVENTDATA(), что конкретно его торкнуло.

if exists(select 1 from sys.triggers where parent_class_desc = 'database' and name = 'tr1')

drop trigger tr1 on database

create trigger tr1 on database for DDL_DATABASE_LEVEL_EVENTS as

select EVENTDATA()

if OBJECT_ID('t', 'U') is not null drop table t

create table t(id int)

<EVENT_INSTANCE>

<EventType>CREATE_TABLE</EventType>

<LoginName>W7X86SQL08R2\Administrator</LoginName>

<DatabaseName>tempdb</DatabaseName>

<ObjectType>TABLE</ObjectType>

<CommandText>create table t(id int)

</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

Скрипт 1

В данном случае видно, что триггер сработал в ответ на событие CREATE_TABLE несмотря на то, что был создан на более общую группу DDL_DATABASE_LEVEL_EVENTS.

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

Рис.1

Из иллюстрации видно, что первые две строчки - результат срабатывания триггера tr1 на DDL_DATABASE_LEVEL_EVENTS (Скрипт 1), за которым последовательно идут tr2 на create_table, alter_table и tr3, tr4 на create_table.

Записывая всякий раз результат EVENTDATA() в таблицу, можно построить мониторинг и журналирование изменений над объектами SQL Server на основе DDL-триггеров, что особенно ценно в стандартной и младших редакциях, где нет оператора create audit.

2. Независимо от редакции в T-SQL нет команды ALTER TABLE ... RENAME TO ... Переименование таблиц и прочих объектов производится при помощи хранимой процедуры sp_rename. Новичков это, как правило, обескураживает. Процедура или оператор - дело десятое. Обескураживает то, что переименование, будучи по сути изменением объекта, не вызывало (до недавних пор) срабатывание триггера на изменение. Объяснялось это тем, что в SQL Server название объекта хранится отдельно. Определение объекта лежит в другом месте. DDL-триггеры настроены на то место, где хранится определение. Изменение названия их не задевало. Однако тем или другим местом это сильно задевало народ, который не желал слушать умных объяснений, а руководствовался здравым смыслом.

3. По многочисленным пожеланиям трудящихся в SQL Server 2008 R2 CTP3 ситуацию исправили - http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283525. Появилось событие RENAME, триггер на которое ловит факт переименования объекта.

use tempdb

if exists(select 1 from sys.triggers where parent_class_desc = 'database' and name = 'tr1')

drop trigger tr1 on database

create trigger tr1 on database for RENAME as

select EVENTDATA()

if OBJECT_ID('t', 'U') is not null drop table t

create table t(id int)

if OBJECT_ID('t1', 'U') is not null drop table t1

exec sp_rename @objname = 't', @newname = 't1', @objtype = 'object'

Скрипт 2

Рис.2

Информация о событии содержит старое и новое имена.

<EVENT_INSTANCE>

<EventType>RENAME</EventType>

<LoginName>W7X86SQL08R2\Administrator</LoginName>

<DatabaseName>tempdb</DatabaseName>

<ObjectType>TABLE</ObjectType>

<Param>object</Param>

</Parameters>

<CommandText>exec sp_rename @objname = 't', @newname = 't1', @objtype = 'object'</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

Скрипт 3

4. sp_rename не была единственным исключением. Например, управление членством в ролях осуществляется при помощи хранимых процедур sp_addsrvrolemember/ sp_dropsrvrolemember для серверных ролей и sp_addrolemember/sp_droprolemember для датабазных. Как и sp_rename, они тоже пролетали мимо кассы. Всякие GRANT/DENY ловились, а добавление или удаление члена из роли не вызывало срабатывание DDL-триггера. Нынче с энтим безобразием тоже покончено.

use master

if exists(select 1 from sys.server_principals where name = 'alexejs') drop login alexejs

create login alexejs with password = 'Abra Cadabrae', check_policy = off, default_database = AdventureWorks2008R2

if exists(select 1 from UserName>

<DatabaseName>AdventureWorks2008R2</DatabaseName>

<ObjectName>alexejs</on all server for ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER as

select EVENTDATA()

use AdventureWorks2008R2

if exists(select 1 from sys.database_principals where name = 'alexejs') drop user alexejs

create user alexejs from login alexejs

if exists(select 1 from sys.triggers where parent_class_desc = 'database' and name = 'tr1')

drop trigger tr1 on database

create trigger tr1 on database for ADD_ROLE_MEMBER, DROP_ROLE_MEMBER as

select EVENTDATA()

exec sp_addrolemember @membername = 'alexejs', @rolename = 'db_datawriter'

exec sp_addsrvrolemember @loginame = 'alexejs', @rolename = 'sysadmin'

---

<EVENT_INSTANCE>

<EventType>ADD_ROLE_MEMBER</EventType>

<LoginName>W7X86SQL08R2\Administrator</LoginName>

<DatabaseName>AdventureWorks2008R2</DatabaseName>

<ObjectName>alexejs</ObjectName>

<SID>NyocvZkMcEmSpfdMFvyHvQ==</SID>

<RoleName>db_datawriter</RoleName>

<CommandText>exec sp_addrolemember @membername = 'alexejs', @rolename = 'db_datawriter'

</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

<EVENT_INSTANCE>

<EventType>ADD_SERVER_ROLE_MEMBER</EventType>

<LoginName>W7X86SQL08R2\Administrator</LoginName>

<ObjectName>alexejs</ObjectName>

<ObjectType>LOGIN</ObjectType>

<DefaultLanguage>us_english</DefaultLanguage>

<DefaultDatabase>AdventureWorks2008R2</DefaultDatabase>

<LoginType>SQL Login</LoginType>

<SID>NyocvZkMcEmSpfdMFvyHvQ==</SID>

<RoleName>sysadmin</RoleName>

<CommandText>exec sp_addsrvrolemember @loginame = 'alexejs', @rolename = 'sysadmin'</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

Скрипт 4

В примере создается логин, создается серверный DDL-триггер tr1 на добавление/удаление логина в серверную роль. Создаем пользователя в базе на основании этого логина. Создаем DDL-триггер tr1 уровня базы на добавление/удаление пользователя в роль БД. Тестируем, включая пользователя в датабазную роль и логина в серверную. Видим, что все поймалось.

Рис.3

5. Домашнее задание.

Задача.

Девушка Юля является администратором сервера isvrus. Желая пройти сертификацию Works with SQL Server 2008, она согласно Требованию 2 решает очистить серверную роль sysadmin от всяких сомнительных личностей.

Рис.4

Какой DDL-триггер на какое событие должен написать пользователь alexejs, чтобы сейчас, когда девушка Юля радостно нажмет кнопку Remove, из сисадминов вынесет не его, а кого-то другого?