欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-11-21 12:26:55
复制代码 代码如下:/****** 对象: DdlTrigger [Trig_DBVersionController] 脚本日期: 09/05/2008 15:50:16...
复制代码 代码如下:

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

下面是存储的库和表结构
提供图片
sql2005可实时监测数据库版本控制SQL的触发器
上面图片可以清淅看到表结构
大家写出表的SQL出来