可实时监测数据库版本控制SQL

最新推荐文章于 2025-12-03 18:58:29 发布

原创最新推荐文章于 2025-12-03 18:58:29 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #function #table #sql #insert #schema

本文介绍了一个SQL Server 2005中的数据库版本控制触发器，该触发器用于记录数据库中各类对象的创建、修改和删除等操作，并将相关信息保存到指定的表中。

用于sql2005

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

下面是存储的库和表结构
提供图片