根据设置的表和字段动态生成触发器记录数据的变动日志

来源:互联网 时间:1970-01-01

本SQL代码主要功能为根据业务人员设置的监控字段(保存在数据库AuditLogType表的监控规则); 减去了数据表操作日志代码的编写;并且系统管理员可以根据数据库服务器性能自由调整监控字段的量。可以做到一次开发;多次使用。

以下代码是生成日志表(AuditLogging)和设置监控字段表(AuditLogType);

GetUserID 函数为获得当前操作数据库的用户;此函数可以和业务系统的用户对接;可以在找不到业务系统用户的情况下;获得数据库的SUSER_NAME(如sa等数据库用户);这样不管是业务系统修改数据;还是从数据库直接修改数据都可以记录。

缺点是监控表设置太多,生成太多的触发器,将影响服务器的性能;日志表(AuditLogging)需要定期转存或者清除;提升日志表(AuditLogging)查询速度。

CREATE TABLE [dbo].[AuditLogging]( [ID] [int] IDENTITY(1,1) NOT NULL, [MessageID] [int] NULL, [CreateID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogging_CreateID] DEFAULT ([dbo].[GetUserID]()), [RecordID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogging_RecordID] DEFAULT ([dbo].[GetUserID]()), [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogging_CreateDate] DEFAULT (getdate()), [RecordDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogging_RecordDate] DEFAULT (getdate()), [RowPointer] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AuditLogging_RowPointer] DEFAULT (newid()), [Stat] [nvarchar](10) NULL, [TableName] [nvarchar](50) NOT NULL, [TableRowPointer] [uniqueidentifier] NULL, [FieldName] [nvarchar](50) NULL, [OldValue] [nvarchar](4000) NULL, [NewValue] [nvarchar](4000) NULL, [KeyValue] [nvarchar](4000) NULL, CONSTRAINT [PK_AuditLogging] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[AuditLogType]( [MessageID] [int] IDENTITY(1,1) NOT NULL, [Stat] [nvarchar](50) NULL CONSTRAINT [DF_AuditLogType_Stat] DEFAULT (''), [Enabled] [bit] NULL CONSTRAINT [DF_AuditLogType_Enabled] DEFAULT ((0)), [TableName] [nvarchar](50) NOT NULL, [FieldName] [nvarchar](50) NOT NULL, [CreateID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogType_CreateID] DEFAULT ([dbo].[GetUserID]()), [RecordID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogType_UpID] DEFAULT ([dbo].[GetUserID]()), [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogType_CreateDate1] DEFAULT (getdate()), [RecordDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogType_UpDate] DEFAULT (getdate()), [RowPointer] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AuditLogType_RowPointer] DEFAULT (newid()), CONSTRAINT [PK_AuditLogType_1] PRIMARY KEY CLUSTERED ( [MessageID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]


相关阅读:
Top