• sqlserver 数据库操作记录 实现


    原理: 通过创建一个记录表和一个触发器相结合实现的

    创建表:VERSION_CONTROL_TABLE

    CREATE TABLE [dbo].[VERSION_CONTROL_TABLE](
        [ID] [bigint] IDENTITY(1,1) NOT NULL,
        [databasename] [varchar](256) NULL,
        [eventtype] [varchar](50) NULL,
        [objectname] [varchar](256) NULL,
        [objecttype] [varchar](25) NULL,
        [sqlcommand] [nvarchar](max) NULL,
        [loginname] [varchar](256) NULL,
        [hostname] [varchar](256) NULL,
        [PostTime] [datetime] NULL,
        [Version] [int] NOT NULL,
     CONSTRAINT [PK_VERSION_CONTROL_TABLE] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    

      创建触发器:TRG_VERSION_CONTROL_TABLE

    CREATE TRIGGER [TRG_VERSION_CONTROL_TABLE]
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
    CREATE_INDEX, ALTER_INDEX, DROP_INDEX
    
    AS
    
    SET NOCOUNT ON
    
    DECLARE @CurrentVersion int
    DECLARE @CurrentID int
    DECLARE @DatabaseName varchar(256)
    DECLARE @ObjectName varchar(256)
    DECLARE @data XML
    
    SET @data = EVENTDATA()
    INSERT INTO dbo.VERSION_CONTROL_TABLE(databasename, eventtype,objectname, objecttype, sqlcommand, loginname,Hostname,PostTime, Version)
    VALUES(
    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),  -- value is case-sensitive
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
    HOST_NAME(),
    GETDATE(),
    0
    )
    SET @CurrentID = IDENT_CURRENT('VERSION_CONTROL_TABLE')
    SELECT @DatabaseName = databasename, @ObjectName = objectname FROM VERSION_CONTROL_TABLE WHERE ID = @CurrentID
    IF (@DatabaseName IS NOT NULL AND @ObjectName IS NOT NULL)
    BEGIN
        SELECT @CurrentVersion = MAX(Version) FROM VERSION_CONTROL_TABLE WHERE databasename = @DatabaseName AND objectname = @ObjectName
        UPDATE VERSION_CONTROL_TABLE SET Version = ISNULL(@CurrentVersion, 0) + 1 WHERE ID = @CurrentID
    END
    

      

    测试: 

              SELECT * FROM dbo.VERSION_CONTROL_TABLE

    注释: 文章灵感来自: http://www.cnblogs.com/unruledboy/archive/2011/12/05/SQLMon2.html

              和                  http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

  • 相关阅读:
    每个Java开发人员都应该知道的4个Spring注解
    JVM中的动态语言支持简介
    深入探索Java设计模式(五)之构建器模式
    Java——MVC模式
    程序出了问题,报错只能参考
    查看电脑端口占用情况
    Java——参数传递
    Python——关于定义过程
    Java——super的使用
    关于如何查看论文是否被SCI或者EI收录
  • 原文地址:https://www.cnblogs.com/2zhyi/p/3090339.html
Copyright © 2020-2023  润新知