sql2005可实时监测数据库版本控制SQL的触发器

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