以下针对某个数据库在创建数据表时调用触发器，并将创建该数据表的用户账户写入到Windows的Event Log中。
|
1
2
3
4
5
6
7
|
CREATE
TRIGGER
reminder
ON
DATABASE
FOR
CREATE_TABLE
AS
DECLARE
@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
AS
PRINT 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
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
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
]
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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'
GO
EXEC
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
BEGIN
SET
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;
ELSE
PRINT
' '
+ @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
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
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
GO
SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_PADDING
ON
GO
CREATE
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
]
GO
SET
ANSI_PADDING
ON
GO
ALTER
TABLE
[dbo].[Test]
WITH
CHECK
ADD
CONSTRAINT
[FK_Test_DetailID]
FOREIGN
KEY
([DetailID])
REFERENCES
[dbo].[TestDetail] ([ID])
GO
ALTER
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
AS
DECLARE
@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'
BEGIN
DECLARE
@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，如需转载请自行联系原作者
