SQL Server DDL triggers (Trigger)-Create a database-level DDL trigger

SQL Server DDL triggers (Trigger)-Create a database-level DDL trigger

The following invokes a trigger for a database when it is created, and writes the user account that created the data table to the event log in Windows.

Create TRIGGER Reminderon databasefor create_tableasdeclare @str NVARCHAR (+) SET @str =suser_sname () + N ' CREATE a new tabl E ' RAISERROR (@str, 10,1) with LOG

After the DDL trigger is created, it appears in a different location in the Object Explorer because of the level of the trigger, which is the trigger for creating the database level, so it appears in the "Programmability" database Triggers "node.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M01/57/BF/wKiom1Sjxp7B29YxAAEpijOWhIs403.jpg "border=" 0 "height=" 456 "/>

After you create the DDL trigger, try to create the data table using the following syntax, which writes information to the Windows operating system event log because the DDL trigger uses the RAISERROR system function with the WITH LOG option. Next, you can query the event content in Event Viewer.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/57/BD/wKioL1Sjx1bCUeFBAAKyJkTcKGU783.jpg "border=" 0 "height=" 569 "/>

Since the trigger is executed by default in the same transaction as the syntax package that raised the trigger, we can rollback the effect of the previous instruction on the system with the rollback command so that the user's drop_table, alter_table DDL syntax cannot be executed within the DB.

CREATE TRIGGER Safetyon databasefor drop_table, Alter_tableasprint N ' before drop or ALTER table,you should drop TRIGGER SA Fety!!!! ' ROLLBACK

After completing the throttling DDL trigger, we test the trigger with the following syntax:

ALTER TABLE tblabc ADD C2 INT

SQL server returns an error message because the trigger is rolled back directly (Rollback).

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M01/57/BD/wKioL1Sjx1aSQj8fAABjaWMWTNo846.jpg "border=" 0 "height="/>

In addition, the DDL trigger is paired with the eventdata system function, which is queried through the query function of the XQuery statement provided by SQL Server 2005 to remove the desired data. All DDL behaviors are then logged to another data table.

 

USE [ADVENTUREWORKS2012]GO/****** OBJECT: TABLE [DBO]. [databaselog] script date: 2014/12/31 11:33:19 ******/set ansi_nulls  ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [DBO]. [DatabaseLog] ([databaselogid] [int] identity]  NOT NULL,[PostTime] [datetime] NOT  null,[databaseuser] [sysname] not null,[event] [sysname] not null,[schema]  [Sysname] null,[object] [sysname] null,[tsql] [nvarchar] (max)  not null,[xmlevent ] [xml] not null,constraint [pk_databaselog_databaselogid] primary key  Nonclustered ([Databaselogid] asc) with  (pad_index = off, statistics_norecompute =  off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks  = on)  on [primary])  on [primary] textimage_on [primary]goexec sys.sp_addextendedproperty  @name =n ' ms_description ',  @value =n ' primary key  For databaselog records. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' databaselogid ' goexec sys.sp_addextendedproperty  @name =n ' MS _description ',  @value =n ' the date and time the ddl change occurred. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' posttime ' goexec sys.sp_addextendedproperty  @name =n ' Ms_ Description ',  @value =n ' The user who implemented the ddl change. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' databaseuser ' goexec sys.sp_addextendedproperty  @name =n ' MS_description ',  @value =n ' the type of ddl statement that was executed. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' Event ' goexec sys.sp_addextendedproperty  @name =n ' Ms_ Description ',  @value =n ' the schema to which the changed object belongs. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' Schema ' goexec sys.sp_addextendedproperty  @name =n ' Ms_ Description ',  @value =n ' the object that was changed by the ddl  Statment. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' Object ' goexec sys.sp_addextendedproperty  @name =n ' Ms_ Description ',  @value =n 'the exact transact-sql statement that was executed. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' TSQL ' goexec sys.sp_addextendedproperty  @name =n ' Ms_ Description ',  @value =n ' The raw xml data generated by database trigger. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',   @level2type =n ' COLUMN ', @level2name =n ' xmlevent ' goexec sys.sp_addextendedproperty  @name =n ' Ms_ Description ',  @value =n ' audit table tracking all ddl changes made to  the adventureworks database. data is captured by the database  trigger ddldatabasetriggerlog. '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ' Goexec sys.sp_addextendedproperty  @name =n ' ms_description ',  @value =n ' primary key  (nonclustered)   Constraint '  ,  @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ',  @level2type =n ' CONSTRAINT ', @level2name =n ' Pk_databaselog_databaselogid ' GO

 

create trigger [ddldatabasetriggerlog] on databasefor ddl_database_level_events  asbeginset nocount on;declare  @data  XML;DECLARE  @schema  sysname;declare @ object sysname;declare  @eventType  sysname; set  @data  = eventdata (); set  @eventType  =  @data. Value (' (/event_instance/eventtype) [1] ',  ' sysname '); set  @schema  =  @data. Value (' (/event_instance/schemaname) [1] ',  ' sysname '); set  @object  =  @data. Value (' (/event_instance/objectname) [1] ',  ' sysname ') if  @object  IS NOT NULLPRINT  '   '  +  @eventType  +  '  -  '  +  @schema  +  '. '  +  @object; elseprint  '    +  @eventType  +  '  -  '  +  @schema; if @ Eventtype is nullprint convert (nvarchar (max),  @data); insert [dbo]. [DatabaseLog] ([Posttime],[databaseuser],[evenT],[schema],[object],[tsql],[xmlevent]) VALUES (GETDATE (), convert (Sysname, current_user), @eventType, convert (sysname,  @schema), CONVERT (sysname,  @object), @data. Value (' (/event_instance/tsqlcommand) [1] ',  ' nvarchar (max) '), @data); END; GO

CREATE TABLE testtable (a int); ALTER table testtable ADD b nvarchar (ten);D ROP table testtable; GO

SELECT * FROM dbo. DatabaseLog ORDER by Databaselogid;

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M02/57/BD/wKioL1Sjx1fgBp67AAGEhP64mzc461.jpg "border=" 0 "height="/>

To delete the data table that holds the log generated by the trigger, dbo. DatabaseLog, you must first delete the trigger that uses this data table. Otherwise, there will be strange error messages as follows:

650) this.width=650; "title=" clip_image005 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M00/57/BD/wKioL1Sjx1ij7IaqAABc_tJSkSk601.jpg "border=" 0 "height="/>

Of course, before you delete any objects, you can check the object dependencies to make sure you can delete them.

Create a table with a primary foreign key shutdown to test the use of DDL triggers to avoid data tables being deleted.

USE [ADVENTUREWORKS2012]GO/****** OBJECT: TABLE [DBO]. [test] script date: 2014/12/31 15:10:19 ******/set ansi_nulls ongoset  QUOTED_IDENTIFIER ONGOCREATE TABLE [DBO]. [Test] ([Id] [int] identity ( not null,[detailid] [int] null,constraint [pk_) Test] primary key clustered ([Id] asc) with  (pad_index = off, statistics _norecompute = off, ignore_dup_key = off, allow_row_locks = on,  Allow_page_locks = on)  on [primary])  ON [PRIMARY]GO/****** Object:  TABLE [DBO]. [Testdetail] script date: 2014/12/31 15:10:19 ******/set ansi_nulls ongoset  QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [DBO]. [Testdetail] ([id] [int] identity]  not null,[desc] [varchar] ( nul)L,constraint [pk_testdetail] primary key clustered ([Id] asc) WITH  (PAD_INDEX  = off, statistics_norecompute = off, ignore_dup_key = off, allow _row_locks = on, allow_page_locks = on)  on [primary])  ON [PRIMARY ]GOSET ANSI_PADDING ONGOALTER TABLE [DBO]. [Test] with check add constraint [fk_test_detailid] foreign key ([DetailID]) REFERENCES [DBO]. [testdetail]  ([ID]) goalter table [dbo]. [Test] check constraint [fk_test_detailid]go

With EventData system functions and XQuery statements to resolve their contents, for example, the value function to determine the object name, and then decide whether to allow the user to delete. The safety trigger is modified under if the user deletes the dbo. Test data table before the error message appears.

ALTER TRIGGER Safetyon databasefor drop_tableasdeclare @data xml=eventdata () DECLARE @SchemaName nvarchar (max) DECLARE @ TableName nvarchar (max) set @SchemaName =eventdata (). Value (' (/event_instance/schemaname) [1] ', ' sysname ') set @ Tablename=eventdata (). Value (' (/event_instance/objectname) [1] ', ' sysname ') IF @SchemaName = ' dbo ' and @TableName = ' Test ' Begindeclare @msg NVARCHAR (MAX) =n ' You can ' t Delete the table: [' + @SchemaName + ']. [' + @TableName + '] ' RAISERROR (@msg, 16,1) ROLLBACK tranend

Normally, the data table is protected by foreign key constraints, but cannot be deleted, but the foreign key constraint only protects the main data table, which corresponds to dbo. Testdetail, instead of protecting the reference data table, this corresponds to dbo. Test.

650) this.width=650; "title=" clip_image006 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M02/57/BD/wKioL1Sjx1mBcmWrAACilTBlZRo424.jpg "border=" 0 "height=" 282 "/>

The Target data table can be protected by using value in XQuery, obtaining schemaname and objectname, and then judging whether the object is to be protected by a hypothetical sentence. At this point, execute the following statement:

DROP TABLE dbo. Test

You will get the following error message:

650) this.width=650; "title=" clip_image007 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M01/57/BF/wKiom1SjxqORGx1jAACRVNgnqEk022.jpg "border=" 0 "height="/>

To remove a database-level DDL trigger, simply refer to the following syntax:

DROP TRIGGER safety on Databasedrop TABLE dbo. Test

When you delete a DDL trigger, you want to match the on database or the on all server option, or SQL Server will assume that you want to delete the generic DML trigger, and therefore return an error message that the object cannot be found.

To view the information recorded by the [Ddldatabasetriggerlog] trigger:

SELECT * FROM dbo. DatabaseLog ORDER by Databaselogid

650) this.width=650; "title=" clip_image008 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M02/57/BF/wKiom1SjxqSTqFYdAALh7Tf3omI218.jpg "border=" 0 "height=" 149 "/>

Finally, the XML content returned by the EVENTDATA function is shown below.

<EVENT_INSTANCE><EventType>DROP_TABLE</EventType><PostTime>2014-12-31T15:30:01.010< /posttime><spid>53</spid><servername>win-llpkr5buv6s</servername><loginname> Win-llpkr5buv6s\administrator</loginname><username>dbo</username><databasename> adventureworks2012</databasename><schemaname>dbo</schemaname><objectname>test</ Objectname><objecttype>table</objecttype><tsqlcommand><setoptions ANSI_NULLS= "on" ANSI_ null_default= "on" ansi_padding= "on" quoted_identifier= "on" encrypted= "FALSE"/><commandtext>drop TABLE dbo. Test</commandtext></tsqlcommand></event_instance>

In addition, SQL Server provides a way for the trigger to query metadata, which was created as an object and the query syntax is as follows:

SELECT * from sys.triggers WHERE name= ' safety ' select definition from Sys.sql_moduleswhere object_id= (select object_id FRO M sys.triggers WHERE name= ' safety ')

650) this.width=650; "title=" clip_image009 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M00/57/BD/wKioL1Sjx1uDZZpKAAHIa2VNjXM626.jpg "border=" 0 "height=" 118 "/>

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1598185

SQL Server DDL triggers (Trigger)-Create a database-level DDL trigger