Microsoft® SQL Server® 2012 Bible

903

Chapter 36: Creating Triggers

36

The third DDL trigger traps only create table commands:

CREATE TRIGGER DDL_Create_Table_Sample

ON DATABASE

FOR Create_Table

AS

Set NoCount ON

Print 'DDL_Create_Table_Sample DDL Trigger'

With these three DDL triggers installed, the next few DDL commands demonstrate DDL trig-

ger scope. Creating a new database is a server-level event:

Create database Testdb

Result:

DDL_Server_Level_Sample DDL Trigger

Creating a new table fi res the create table DDL trigger as well as the general database

DDL events trigger:

create table Test (col1 INT)

Result:

DDL_Database_Sample DDL Trigger

DDL_Create_Table_Sample DDL Trigger

Dropping the table fi res the general database DDL event trigger, but not the specifi c

create table event trigger:

drop table Test

Result:

DDL_Database_Sample DDL Trigger

DDL Triggers and Security

The DDL trigger creation options, ENCRYPTION and EXECUTE AS, both ensure the security

of system-level auditing triggers. The following DDL trigger will be encrypted when stored:

CREATE TRIGGER DDL_DDL_Level_Sample

ON ALL SERVER

WITH ENCRYPTION

FOR DDL_EVENTS

AS

code

As with stored procedures, triggers can be executed under a different security context.

Instead of the user who issued the DDL command that caused the DDL trigger to fi re, the

trigger can execute as one of the following security contexts:

c36.indd 903c36.indd 903 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM

http://www.it-ebooks.info