数据库结构同步之通过DDL触发器记录数据库结构的变更

需求： 在开发多人协作的项目的时候，一般要同时使用多个数据库 常见的情况有： 一个开发者用的数据库（开发库）， 一个测试者用的数据库（测试库）， 一个正式开放给客户的数据库（正式库）， 那么这三个数据库之间的数据结构的同步就将成为一个问题 如：当

需求：

在开发多人协作的项目的时候，一般要同时使用多个数据库

常见的情况有：

一个开发者用的数据库（开发库），

一个测试者用的数据库（测试库），

一个正式开放给客户的数据库（正式库），

那么这三个数据库之间的数据结构的同步就将成为一个问题

如：当开发者A在“开发库”中添加了一个表，开发者B修改了一个表...

这些数据库结构的变更势必要同步到“测试库”和“正式库”中去

但肉手记录数据库结构变更的方式即麻烦由容易出错...

如之奈何？

 

思考过程：

之前曾在数据库达人邹建那里看到一篇帖子（找不到了），

但试过之后发现，很多情况都会出异常...就放弃了

后来在WCF达人Artech这里看到一篇文章

追踪记录每笔业务操作数据改变的利器——SQLCDC

此文章是针对数据库表记录CURD操作的日志

并非表结构

后来我在文章评论中向Artech请教了我的问题，

得到了DDL Trigger的答案 

记下来并分享

 

代码及解释：

USE MRLH_CM;
GO
--创建记录数据库结构变更的表
CREATE TABLE LogTable (DB_User nvarchar(200), EventType nvarchar(200), SQLString nvarchar(2000),ChangeTime datetime);
GO
CREATE TRIGGER LogTrigger 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE ,CREATE_TABLE
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT LogTable 
   (DB_User, EventType, SQLString,ChangeTime) 
   VALUES 
   (CONVERT(nvarchar(100), CURRENT_USER), 
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
   GETDATE()
    ) ;
GO

 和普通的创建触发器的过程类似

就说其中的两个地方吧

1.FOR DROP_TABLE, ALTER_TABLE ,CREATE_TABLE
　　这里只记录了这几个事件

　　如果记录更多的事件请使用

　　FOR DDL_DATABASE_LEVEL_EVENTS

　　了解更多的事件情况请访问

　　http://msdn.microsoft.com/en-us/library/ms186456(SQL.90).aspx

2.SET @data = EVENTDATA()
　　EVENTDATA()是数据库自身的方法

　　返回有关服务器或数据库事件的信息（XML格式）　　

　　只有直接在 DDL 或登录触发器内部引用 EVENTDATA 时，EVENTDATA 才会返回数据。

　　如果 EVENTDATA 由其他例程调用（即使这些例程由 DDL 或登录触发器进行调用），将返回 NULL。

　　@data.value('(/EVENT_INSTANCE/EventType)[1]

　　这是使用XQUERY检索XML中的数据

　　详细的XQUERY教程请看这里

　　http://www.w3school.com.cn/xquery/index.asp

注意：

--想删除表LogTable必须先删除这个触发器
DROP TRIGGER LogTrigger
on database
GO
--删除表
DROP TABLE LogTable
GO

以上代码均在MSSQLSERVER2008下测试通过

其他数据库没有测试