复制代码 代码如下:

/******

对象: DdlTrigger [Trig_DBVersionController]

脚本日期: 09/05/2008 15:50:16

作用: 数据库版本控制

******/

IF EXISTS (SELECT * FROM sys.triggers WHERE name = N'Trig_DBVersionController' AND parent_class=0)

DROP TRIGGER [Trig_DBVersionController] ON DATABASE

GO

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)

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

SELECT @dbid = dbid FROM sys.sysdatabases

WHERE name = @dbName

--获取客户机的机器名

SELECT @clientUser=hostname FROM master..sysprocesses WHERE spid=@spid

-- add version record

INSERT INTO DBController.dbo.tbl_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)'),

''

)

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [Trig_DBVersionController] ON DATABASE

GO

上面是trigger

下面是存储的库和表结构

提供图片

上面图片可以清淅看到表结构

大家写出表的SQL出来