以下针对某个数据库在创建数据表时调用触发器，并将创建该数据表的用户账户写入到Windows的Event Log中。
|
1
2
3
4
5
6
7
|
CREATE TRIGGER reminder
ON DATABASE
FOR CREATE_TABLE
ASDECLARE @str NVARCHAR(100)
SET @str=suser_sname() + N'create a new table'
RAISERROR(@str,10,1) WITH LOG
|
创建完DDL触发器后，因该触发器所在的等级，而会显示在“Object Explorer”中不同的位置，上述是创建数据库等级的触发器，因此，显示在某个数据库的“Programmability”中“Database Triggers”节点之下。
创建完DDL触发器后，请尝试使用下列语法创建数据表，由于该DDL触发器使用RAISERROR系统函数搭配WITH LOG选项，这会将信息写入到Windows操作系统的事件日志中。接着，可以在事件查看器中查询到事件内容。
由于触发器默认都是与引发该触发器的语法包在相同事务内一起执行，因此，我们可以通过ROLLBACK命令回滚先前指令对系统的影响，让用户的DROP_TABLE、ALTER_TABLE DDL语法无法在DB内执行。
|
1
2
3
4
5
6
|
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
ASPRINT N'Before drop or alter table,you should drop trigger safety!!!!'
ROLLBACK |
完成限制的DDL触发器之后，我们通过下列语法测试该触发器：
|
1
|
ALTER TABLE tblAbc ADD c2 INT
|
由于直接被触发器回滚（Rollback），SQL Server将返回错误信息。
另外，通过DDL触发器搭配EVENTDATA系统函数，经由SQL Server 2005所提供的XQuery语句的Query函数查询，取出想要的数据。再将所有DDL行为记录到另外一个数据表中。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
USE [AdventureWorks2012]GO/****** Object: Table [dbo].[DatabaseLog] Script Date: 2014/12/31 11:33:19 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[DatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) 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 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGINSET 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 NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSEPRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT 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 |
|
1
2
3
4
|
CREATE TABLE TestTable(a int);
ALTER TABLE TestTable ADD b nvarchar(10);
DROP TABLE TestTable;
GO |
|
1
|
SELECT * FROM dbo.DatabaseLog ORDER BY DatabaseLogID;
|
若要删除存放触发器所产生log的数据表dbo.DatabaseLog时，必须先删除使用到这个数据表的触发器。否则会有奇怪的错误信息如下：
当然，你在删除任何对象前，可以先检查一下对象依赖关系，以确认是否可以删除。
创建具有主外键关机的表，来测试利用DDL触发器避免数据表被删除。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
USE [AdventureWorks2012]GO/****** Object: Table [dbo].[Test] Script Date: 2014/12/31 15:10:19 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) 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 ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[TestDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Desc] [varchar](50) NULL,
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 ON
GOALTER 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 |
搭配EventData系统函数与XQuery语句解析其内容，例如，以VALUE函数判断对象名称，而后决定是否允许用户删除。将safety触发器修改了下，如果用户删除的是dbo.Test数据表，才会出现错误信息。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
ALTER TRIGGER safety
ON DATABASE
FOR DROP_TABLE
ASDECLARE @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 TRAN
END |
正常情况而言，数据表会被FOREIGN KEY条件约束保护，而无法删除，但是FOREIGN KEY约束只保护主数据表，这里对应dbo.TestDetail，而不保护参照数据表，这里对应dbo.Test。
利用XQuery中的Value，取得SchemaName以及ObjectName，再通过假设句判断是否为所要保护的对象，即可保护目标数据表。此时，若执行如下的语句：
|
1
|
DROP TABLE dbo.Test
|
将会得到如下的错误信息：
若要删除数据库级别的DDL触发器，只要参照如下语法即可：
|
1
2
|
DROP TRIGGER safety ON DATABASE
DROP TABLE dbo.Test
|
在删除DDL触发器时，要搭配ON DATABASE或ON ALL SERVER选项，否则SQL Server会以为要删除的是一般DML触发器，因此，会返回找不到对象的错误信息。
查看[ddlDatabaseTriggerLog]触发器记录的信息：
|
1
|
SELECT * FROM dbo.DatabaseLog ORDER BY DatabaseLogID
|
最后，显示EVENTDATA函数返回的XML内容如下。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<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>
|
另外，SQL Server也针对触发器提供查询元数据的方式，以前述创建的DDL触发器为对象，查询语法如下：
|
1
2
3
|
SELECT * FROM sys.triggers WHERE name=’safety’
SELECT definition FROM sys.sql_modules
WHERE object_id=(SELECT object_id FROM sys.triggers WHERE name=’safety’)
|
本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1598185，如需转载请自行联系原作者