/****** 
对象: 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