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

需求：

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

常见的情况有：

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

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

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

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

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

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

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

如之奈何？

 

思考过程：

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

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

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

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

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

并非表结构

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

得到了DDL Trigger的答案 

记下来并分享

 

代码及解释：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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下测试通过

其他数据库没有测试