SQL Server DDL 触发器（Trigger）-- 创建数据库级别的DDL触发器

浏览数：23 / 时间：2015年06月12日

SQL Server DDL 触发器（Trigger）-- 创建数据库级别的DDL触发器

以下针对某个数据库在创建数据表时调用触发器，并将创建该数据表的用户账户写入到Windows的Event Log中。

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内执行。

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触发器之后，我们通过下列语法测试该触发器：

ALTER TABLE tblAbc ADD c2 INT

由于直接被触发器回滚（Rollback），SQL Server将返回错误信息。

另外，通过DDL触发器搭配EVENTDATA系统函数，经由SQL Server 2005所提供的XQuery语句的Query函数查询，取出想要的数据。再将所有DDL行为记录到另外一个数据表中。

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

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

CREATE TABLE TestTable(a int);
ALTER TABLE TestTable ADD b nvarchar(10);
DROP TABLE TestTable;
GO

SELECT * FROM dbo.DatabaseLog ORDER BY DatabaseLogID;

若要删除存放触发器所产生log的数据表dbo.DatabaseLog时，必须先删除使用到这个数据表的触发器。否则会有奇怪的错误信息如下：

当然，你在删除任何对象前，可以先检查一下对象依赖关系，以确认是否可以删除。

创建具有主外键关机的表，来测试利用DDL触发器避免数据表被删除。

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数据表，才会出现错误信息。

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，再通过假设句判断是否为所要保护的对象，即可保护目标数据表。此时，若执行如下的语句：

DROP TABLE dbo.Test

将会得到如下的错误信息：

若要删除数据库级别的DDL触发器，只要参照如下语法即可：

DROP TRIGGER safety ON DATABASE
DROP TABLE dbo.Test

在删除DDL触发器时，要搭配ON DATABASE或ON ALL SERVER选项，否则SQL Server会以为要删除的是一般DML触发器，因此，会返回找不到对象的错误信息。

查看[ddlDatabaseTriggerLog]触发器记录的信息：

SELECT * FROM dbo.DatabaseLog ORDER BY DatabaseLogID

最后，显示EVENTDATA函数返回的XML内容如下。

<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触发器为对象，查询语法如下：

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