DDL-тригер і перейменування об’єкта, Інші СУБД, Бази даних, статті

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

go

create trigger tr1 on database for DDL_DATABASE_LEVEL_EVENTS as

select EVENTDATA()

go

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

create table t(id int)

go

<EVENT_INSTANCE>

  <EventType>CREATE_TABLE</EventType>

  <PostTime>2009-12-05T01:33:09.550</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2Administrator</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>tempdb</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>t</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS=”ON” ANSI_NULL_DEFAULT=”ON” ANSI_PADDING=”ON” QUOTED_IDENTIFIER=”ON” ENCRYPTED=”FALSE” />

    <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

go

create trigger tr1 on database for RENAME as

select EVENTDATA()

go

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

create table t(id int)

go

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>

  <PostTime>2009-12-05T02:10:19.673</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2Administrator</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>tempdb</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>t</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TargetObjectName />

  <TargetObjectType />

  <NewObjectName>t1</NewObjectName>

  <Parameters>

    <Param>t</Param>

    <Param>t1</Param>

    <Param>object</Param>

  </Parameters>

  <TSQLCommand>

    <SetOptions ANSI_NULLS=”ON” ANSI_NULL_DEFAULT=”ON” ANSI_PADDING=”ON” QUOTED_IDENTIFIER=”ON” ENCRYPTED=”FALSE” />

    <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()

go

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

go

create trigger tr1 on database for ADD_ROLE_MEMBER, DROP_ROLE_MEMBER as

select EVENTDATA()

go

exec sp_addrolemember @membername = “alexejs”, @rolename = “db_datawriter”

exec sp_addsrvrolemember @loginame = “alexejs”, @rolename = “sysadmin”

<EVENT_INSTANCE>

  <EventType>ADD_ROLE_MEMBER</EventType>

  <PostTime>2009-12-05T02:45:58.893</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2Administrator</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>AdventureWorks2008R2</DatabaseName>

  <ObjectName>alexejs</ObjectName>

  <ObjectType>SQL USER</ObjectType>

  <DefaultSchema>dbo</DefaultSchema>

  <SID>NyocvZkMcEmSpfdMFvyHvQ==</SID>

  <RoleName>db_datawriter</RoleName>

  <TSQLCommand>

    <SetOptions ANSI_NULLS=”ON” ANSI_NULL_DEFAULT=”ON” ANSI_PADDING=”ON” QUOTED_IDENTIFIER=”ON” ENCRYPTED=”FALSE” />

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

</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

<EVENT_INSTANCE>

  <EventType>ADD_SERVER_ROLE_MEMBER</EventType>

  <PostTime>2009-12-05T02:45:58.897</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2Administrator</LoginName>

  <ObjectName>alexejs</ObjectName>

  <ObjectType>LOGIN</ObjectType>

  <DefaultLanguage>us_english</DefaultLanguage>

  <DefaultDatabase>AdventureWorks2008R2</DefaultDatabase>

  <LoginType>SQL Login</LoginType>

  <SID>NyocvZkMcEmSpfdMFvyHvQ==</SID>

  <RoleName>sysadmin</RoleName>

  <RoleSID>Aw==</

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*