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