SQL SERVER 2005支持DDL触发器,不过网上的介绍的应用场合一般都是来阻止非法用户对数据库结构的修改,其实既然可以监视数据结构的变化,就可以记录数据结构更改记录咯。于是就做了个数据库版本控制的小工具,主要就是利用DDL触发器来捕捉提交的更改。
1.创建控制支撑的数据库DBController记录数据库更改历史。
数据库核心表 t_version_details
2.核心触发器
trigger sql:
CREATE
TRIGGER
Trig_DBVersionController
ON
DATABASE
FOR
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
SET
NOCOUNT
ON
DECLARE
@data
AS
xml
DECLARE
@clientUser
AS
nvarchar
(
128
)
DECLARE
@spid
AS
nvarchar
(
128
)
DECLARE
@serverName
AS
nvarchar
(
128
)
DECLARE
@dbName
AS
nvarchar
(
128
)
DECLARE
@dbid
AS
int
DECLARE
@objName
AS
nvarchar
(
512
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT
@data
=
EVENTDATA()
SELECT
@spid
=
@data
.value(
'
(/EVENT_INSTANCE/SPID)[1]
'
,
'
nvarchar(128)
'
)
SELECT
@serverName
=
@data
.value(
'
(/EVENT_INSTANCE/ServerName)[1]
'
,
'
nvarchar(256)
'
)
SELECT
@dbName
=
@data
.value(
'
(/EVENT_INSTANCE/DatabaseName)[1]
'
,
'
nvarchar(128)
'
)
SELECT
@objName
=
@data
.value(
'
(/EVENT_INSTANCE/ObjectName)[1]
'
,
'
nvarchar(128)
'
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT
@dbid
=
dbid
FROM
sys.sysdatabases
WHERE
name
=
@dbName
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*获取客户机的机器名*/
SELECT
@clientUser
=
hostname
FROM
master..sysprocesses
WHERE
spid
=
@spid
--
add version record
INSERT
INTO
DBController.dbo.t_version_details
(post_computer_name, sys_dbid, uid,
[
schema
]
,
PostTime, EventType, ObjectType, ObjectName, CommandText, Remark)
VALUES
(
@clientUser
,
@dbid
,
@data
.value(
'
(/EVENT_INSTANCE/LoginName)[1]
'
,
'
nvarchar(256)
'
),
@data
.value(
'
(/EVENT_INSTANCE/UserName)[1]
'
,
'
nvarchar(256)
'
),
@data
.value(
'
(/EVENT_INSTANCE/PostTime)[1]
'
,
'
datetime
'
),
@data
.value(
'
(/EVENT_INSTANCE/EventType)[1]
'
,
'
nvarchar(128)
'
),
@data
.value(
'
(/EVENT_INSTANCE/ObjectType)[1]
'
,
'
nvarchar(128)
'
),
@objName
,
@data
.value(
'
(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]
'
,
'
nvarchar(max)
'
),
''
)
3.只要在目标数据库上创建这个触发器就可以记录版本了