SQL Server 2005 数据修改日志
SQL Server 2005 数据修改日志
方案效果
使用DML触发器，相关的操作记录到EditLog表
USE Northwind GO UPDATE Customers SET CompanyName = 'MicroSoft' WHERE customerid = 'ALFKI'
select * from EditLog where ID = 1
当DML发生在存储过程，并且有传入用户ID时，还可以记录存储过程和用户ID
EXEC dbo.SetDoInfo @DoUserID = 9527, @DoProc = 'HocTest'--写在存储过程开头 UPDATE Customers SET CompanyName = 'IBM' WHERE customerid = 'ANATR'
EXEC dbo.sp_TrgSignal_Clear @pos = 1; -------写在存储过程末尾
select * from EditLog where ID = 4
删除数据时，将删除的数据插入到DelLog表
delete Customers where CustomerID = 'WOLZA' select * from DelLog
通用查询结果，该结果不用额外写查询
可读性更强的专用查询结果，需要额外写查询来支持
实现过程
相关数据库 Northwind、Master
- 创建相关表、函数、和存储过程
见其他脚本 - 利用存储过程 sp_GetUpdateStr 生成触发器脚本和 维护 TbList、TbCol的脚本
--用于生成触发器脚本 EXEC sp_GetUpdateStr @TbName = 'Customers', @TbNameCh = '客户表', @keyCol = 'CustomerID', @colstr = 'CustomerID,CompanyName,ContactName,Address,City,PostalCode,Country,Phone,Fax', @colstrCh = '客户ID,公司,姓名,地址,城市,邮编,国家,电话,传真'
执行的时候用 “以文本格式显示结果”，并把每列显示调到最大
- 对记录的表进行一些新增、修改、删除操作
- 利用存储过程 LogQuery 进行通用查询
EXEC LogQuery @DbName = 'Northwind', @TbName = 'Customers', @Item = 'CompanyName', @value = 'IBM', @bgntime = '2014-04-01', @endtime = '2014-04-30'
- 利用存储过程 VarLogQuery 进行专用查询
EXEC VarLogQuery @DbName = 'Northwind', @TbName = 'Customers', @Item = 'CompanyName', @value = 'IBM', @bgntime = '2014-04-01', @endtime = '2014-04-30'
其他脚本
--用于记录日志 USE Northwind GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO /*创建日志表*/--======================== CREATE TABLE [dbo].[EditLog] ( [ID] [bigint] IDENTITY(1, 1) NOT NULL , [DbName] [varchar](50) NULL , [TbName] [varchar](50) NOT NULL , [KeyCol] [sql_variant] NULL , [KeyCol2] [sql_variant] NULL , [KeyCol3] [sql_variant] NULL , [DoType] [int] NOT NULL , [ColName] [varchar](50) NULL , [OldValue] [sql_variant] NULL , [NewValue] [sql_variant] NULL , [DoWhere] [varchar](100) NULL , [DoUserID] [int] NULL , [CrtDate] [datetime] NOT NULL ) GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name = N'MS_Description' , @value = N'0新增，1修改，2删除' , @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' , @level1name = N'EditLog' , @level2type = N'COLUMN' , @level2name = N'DoType' GO ALTER TABLE [dbo].[EditLog] ADD CONSTRAINT [DF_EditLog_EType] DEFAULT ((0)) FOR [DoType] GO ALTER TABLE [dbo].[EditLog] ADD CONSTRAINT [DF_EditLog_CrtDate] DEFAULT (GETDATE()) FOR [CrtDate] GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DelLog]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [DbName] [varchar](50) NULL, [TbName] [varchar](50) NULL, [DoWhere] [varchar](100) NULL, [DoUserID] [int] NULL, [CrtDate] [datetime] NULL, [Col1] [sql_variant] NULL, [Col2] [sql_variant] NULL, [Col3] [sql_variant] NULL, [Col4] [sql_variant] NULL, [Col5] [sql_variant] NULL, [Col6] [sql_variant] NULL, [Col7] [sql_variant] NULL, [Col8] [sql_variant] NULL, [Col9] [sql_variant] NULL, [Col10] [sql_variant] NULL, [Col11] [sql_variant] NULL, [Col12] [sql_variant] NULL, [Col13] [sql_variant] NULL, [Col14] [sql_variant] NULL, [Col15] [sql_variant] NULL, [Col16] [sql_variant] NULL, [Col17] [sql_variant] NULL, [Col18] [sql_variant] NULL, [Col19] [sql_variant] NULL, [Col20] [sql_variant] NULL, [Col21] [sql_variant] NULL, [Col22] [sql_variant] NULL, [Col23] [sql_variant] NULL, [Col24] [sql_variant] NULL, [Col25] [sql_variant] NULL, [Col26] [sql_variant] NULL, [Col27] [sql_variant] NULL, [Col28] [sql_variant] NULL, [Col29] [sql_variant] NULL, [Col30] [sql_variant] NULL, [Col31] [sql_variant] NULL, [Col32] [sql_variant] NULL, [Col33] [sql_variant] NULL, [Col34] [sql_variant] NULL, [Col35] [sql_variant] NULL, [Col36] [sql_variant] NULL, [Col37] [sql_variant] NULL, [Col38] [sql_variant] NULL, [Col39] [sql_variant] NULL, [Col40] [sql_variant] NULL, [Col41] [sql_variant] NULL, [Col42] [sql_variant] NULL, [Col43] [sql_variant] NULL, [Col44] [sql_variant] NULL, [Col45] [sql_variant] NULL, [Col46] [sql_variant] NULL, [Col47] [sql_variant] NULL, [Col48] [sql_variant] NULL, [Col49] [sql_variant] NULL, [Col50] [sql_variant] NULL ) GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[DelLog] ADD CONSTRAINT [DF__DelLog__CrtDate__571DF1D5] DEFAULT (getdate()) FOR [CrtDate] GO /*创建日志表*/--======================== /*创建配置表*/--======================== CREATE TABLE [dbo].[TbList] ( [TbID] [bigint] IDENTITY(1, 1) NOT NULL , [DbName] [varchar](50) NOT NULL , [TbName] [varchar](50) NOT NULL , [Descript] [varchar](50) NOT NULL , [KeyCol] [varchar](50) NOT NULL , [KeyCol2] [varchar](50) NULL , [KeyCol3] [varchar](50) NULL , CONSTRAINT [PK_TbList] PRIMARY KEY CLUSTERED ([TbID] ASC) ) GO CREATE TABLE [dbo].[TbCol] ( [ID] [bigint] IDENTITY(1, 1) NOT NULL , [TbID] [bigint] NOT NULL , [ColName] [varchar](50) NOT NULL , [Descript] [varchar](50) NOT NULL , [ColType] [varchar](50) NOT NULL , [IsFilter] [bit] NULL , CONSTRAINT [PK_TbCol] PRIMARY KEY CLUSTERED ([TbID] ASC, [ColName] ASC) ) GO EXEC sys.sp_addextendedproperty @name = N'MS_Description' , @value = N'字段类型' , @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' , @level1name = N'TbCol' , @level2type = N'COLUMN' , @level2name = N'ColType' GO EXEC sys.sp_addextendedproperty @name = N'MS_Description' , @value = N'是否作为自定义筛选条件' , @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' , @level1name = N'TbCol' , @level2type = N'COLUMN' , @level2name = N'IsFilter' GO ALTER TABLE [dbo].[TbCol] WITH CHECK ADD CONSTRAINT [FK_TbCol_TbList] FOREIGN KEY([TbID]) REFERENCES [dbo].[TbList] ([TbID]) GO ALTER TABLE [dbo].[TbCol] CHECK CONSTRAINT [FK_TbCol_TbList] GO ALTER TABLE [dbo].[TbCol] ADD CONSTRAINT [DF_TbCol_IsFilter] DEFAULT ((0)) FOR [IsFilter] GO /*创建配置表*/--======================== /*利用上下文信息获取操作用户和执行位置*/--======================== /*设置上下文标志过程*/ CREATE PROC dbo.sp_TrgSignal_Set @guid AS BINARY(16), @pos AS INT AS DECLARE @ci AS VARBINARY(128); SET @ci = ISNULL(SUBSTRING(CONTEXT_INFO(), 1, @pos-1), CAST(REPLICATE(0x00, @pos-1) AS VARBINARY(128))) + @guid + ISNULL(SUBSTRING(CONTEXT_INFO(), @pos+16, 128-16-@pos+1), 0x); SET CONTEXT_INFO @ci; GO /*清除上下文标志过程*/ CREATE PROC dbo.sp_TrgSignal_Clear @pos AS INT AS DECLARE @ci AS VARBINARY(128); SET @ci = ISNULL(SUBSTRING(CONTEXT_INFO(), 1, @pos-1), CAST(REPLICATE(0x00, @pos-1) AS VARBINARY(128))) + CAST(REPLICATE(0x00, 16) AS VARBINARY(128)) + ISNULL(SUBSTRING(CONTEXT_INFO(), @pos+16, 128-16-@pos+1), 0x); SET CONTEXT_INFO @ci; GO /*读取上下文标志过程*/ CREATE PROC dbo.sp_TrgSignal_Get @guid AS BINARY(16) OUTPUT, @pos AS INT AS SET @guid = SUBSTRING(CONTEXT_INFO(), @pos, 16); GO CREATE PROC dbo.SetDoInfo @DoUserID int , @DoProc varchar(50) as BEGIN SET NOCOUNT ON declare @guid binary(100) SET @guid = CAST(ISNULL(@DoProc,'')+'#' + CAST(ISNULL(@DoUserID,0) AS VARCHAR(50)) + '$' as binary(100)) EXEC dbo.sp_TrgSignal_Set @guid = @guid, @pos = 1;----------设置上下文标志，用于告诉触发器操作人员 END GO CREATE PROC dbo.GetDoInfo @DoUserID int OUTPUT, @DoProc varchar(50) OUTPUT as BEGIN DECLARE @signal AS binary(100),@s VARCHAR(100),@DoUserIDs varchar(50); EXEC dbo.sp_TrgSignal_Get @guid = @signal OUTPUT, @pos = 1; IF CHARINDEX('$',@signal) = 0 return set @s = LEFT(@signal,CHARINDEX('$',@signal)-1) SET @DoUserID = STUFF(@s,1,CHARINDEX('#',@s),'') SET @DoProc = LEFT(@s,CHARINDEX('#',@s)-1) END GO /*利用上下文信息获取操作用户和执行位置*/--======================== /*拆分字符串函数*/--=========================== USE MASTER GO CREATE FUNCTION [dbo].[fn_split] ( @c VARCHAR(MAX) , @split VARCHAR(2) ) RETURNS @t TABLE (pos INT, col VARCHAR(20)) AS BEGIN DECLARE @pos INT SET @pos = 0 WHILE (CHARINDEX(@split, @c) <> 0) BEGIN SET @pos += 1 INSERT @t (pos, col) VALUES (@pos, SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1)) SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '') END SET @pos += 1 INSERT @t (pos, col) VALUES (@pos, @c) RETURN END GO /*拆分字符串函数*/--=========================== /*CLR聚合字符串函数*/--======================== sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO CREATE ASSEMBLY [GetSumStrNew] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103007DFF224B0000000000000000E0000E210B010800001000000020000000000000BE2900000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000000000000300000400001000001000000000100000100000000000001000000000000000000000006829000053000000004000003803000000000000000000000000000000000000006000000C000000F82800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C4090000002000000010000000100000000000000000000000000000200000602E7273726300000038030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A029000000000000480000000200050000210000F80700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000360002731100000A7D010000042A0000133002003100000001000011000F01281200000A16FE010A062D0E007201000070281300000A100100027B010000040F01281400000A6F1500000A262A5600027B010000040F017B010000046F1600000A262A0013300200160000000200001100027B010000046F1700000A731800000A0A2B00062A4E0002036F1900000A731A00000A7D010000042A520003027B010000046F1700000A6F1B00000A002A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000B0020000237E00001C0300009003000023537472696E677300000000AC0600000400000023555300B0060000100000002347554944000000C00600003801000023426C6F620000000000000002000001571702000900000000FA013300160000010000001A00000002000000010000000600000004000000010000001B0000000C00000002000000010000000200000000000A00010000000000060032002B000A00630048000600800074000A00BB00A6000600EA00E0000600FC00E000060021010F0106005F014001060073010F0106008C010F010600A7010F010600C2010F010600DB010F010600F4010F01060013020F01060030020F0106005A02470247006E02000006009D027D020600BD027D020600DB022B000A00F10248000A001203480006001903400106002F03400106006E032B000000000001000000000001000100092110001800000005000100010001008E000A005020000000008600A1000E0001006020000000008600C500120001009D20000000008600D00018000200B420000000008600D6001E000300D62000000000E601F70023000300EA2000000000E601090129000400000001003A03000001006803000001007E03000001008B030200090039003A012F0041003A01340049003A012F0051003A012F0059003A012F0061003A012F0069003A012F0071003A012F0079003A012F0081003A012F0089003A01390099003A013F00A1003A010E00A9003A010E00B1003A014400C1003A01AC0019003A010E0021004003B20021004B03B60021005703BC0019006103C00019006103CA00D1007503BC0021003A012F0029008003BC0019003A012F00310009012F002E003B00D5002E001300D5002E002300D5002E002B00DB002E003300F7002E005300F7002E006B0018012E004B00D5002E004300D5002E005B0006012E0063000F0143007B004A00C600D0000480000001000000310E7E4900000000000018000000020000000000000000000000010022000000000002000000000000000000000001003C000000000000000000003C4D6F64756C653E0047657453756D5374722E646C6C0047657453756D537472006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E5465787400537472696E674275696C64657200696E7465726D656469617465526573756C7400496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E6172795772697465720057726974650053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E417474726962757465002E63746F720053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E640056616C7565006765745F49734E756C6C006F705F496D706C69636974006765745F56616C756500417070656E640047726F7570004F626A65637400546F537472696E6700720052656164537472696E6700770000000000010000C0F782B66ED85547A928BD02753889C20008B77A5C561934E0890306120D032000010520010111110520010111080420001111052001011215052001011219042001010E0420010102052001011149042001010805200101115D6101000200000004005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65401F00000520010111650320000205000111110E0320000E052001120D0E03070102052001120D1C04070111110501000000001B010016E78988E69D83E68980E69C892028432920203230303900000E01000947657453756D53747200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000007DFF224B0000000002000000520000001429000014190000525344538B0B616C717F7E45B9EC49CAD003F5FD06000000463A5C7A646C5C446F744E65745C53514C53756D5374725C47657453756D5374725C6F626A5C44656275675C47657453756D5374722E706462000000902900000000000000000000AE290000002000000000000000000000000000000000000000000000A029000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000DC0200000000000000000000DC0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001007E49310E000001007E49310E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0043C020000010053007400720069006E006700460069006C00650049006E0066006F0000001802000001003000300030003000300034006200300000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000470065007400530075006D00530074007200000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003600330033002E0031003800380031003400000000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000470065007400530075006D005300740072002E0064006C006C00000044000F0001004C006500670061006C0043006F0070007900720069006700680074000000487243674062096720002800430029002000200032003000300039000000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000470065007400530075006D005300740072002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000470065007400530075006D00530074007200000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003600330033002E00310038003800310034000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003600330033002E003100380038003100340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE AGGREGATE [dbo].[fn_GetSumStr] (@inputStr [nvarchar](200)) RETURNS[nvarchar](max) EXTERNAL NAME [GetSumStrNew].[JionStr] GO /*CLR聚合字符串函数*/--========================
USE master GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -- Author: DBA谭 -- Create date: -- Description: -- Memo: */ alter PROC sp_GetUpdateStr @TbName varchar(50), @TbNameCh varchar(50) = '', @keyCol varchar(150), @colstr varchar(500), @colstrCh varchar(1000) = '' as BEGIN SET NOCOUNT ON DECLARE @DelColStr varchar(2000),@keycollist varchar(150), @inskeycolvalue varchar(150),@joinkeycolvalue varchar(150), @keycolvalue varchar(150),@keycolname varchar(150),@DelColValue varchar(500) select @DelColStr = master.dbo.fn_GetSumStr(', Col'+CAST(column_id AS VARCHAR(10))), @DelColValue = master.dbo.fn_GetSumStr(', ['+name+']') from sys.columns where object_id= object_id(@TbName) select @keycollist = master.dbo.fn_GetSumStr('[keycol'+isnull(CAST(NULLIF(pos,1) AS VARCHAR(10)),'')+'], '), @inskeycolvalue = master.dbo.fn_GetSumStr('i.['+col +'], '), @keycolvalue = master.dbo.fn_GetSumStr('['+col +'], '), @keycolname = master.dbo.fn_GetSumStr(''''+col +''', '), @joinkeycolvalue = master.dbo.fn_GetSumStr(' i.['+col+'] = d.['+col+'] and ') from master.dbo.fn_split(@keyCol,',') select ' USE ['+db_name()+'] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -- Author: DBA谭 -- Create date: -- Description: -- Memo: */ CREATE TRIGGER trg_'+@TbName+' ON [dbo].['+@TbName+'] for INSERT, UPDATE, DELETE AS BEGIN if @@RowCount <= 0 Return set nocount on declare @DelCnt int, @InsCnt int DECLARE @DoUserID int, @DoProc varchar(50) --读取上下文信息 EXEC dbo.GetDoInfo @DoUserID OUTPUT, @DoProc OUTPUT SELECT @DelCnt = COUNT(1) FROM deleted SELECT @InsCnt = COUNT(1) FROM inserted IF @DelCnt > 0 and @InsCnt > 0 BEGIN --插入修改日志 ' UNION ALL select CASE WHEN is_identity = 1 THEN ' if UPDATE(['+name+']) BEGIN raiserror(''不允许更新'+name+''',11,1) ROLLBACK END ' ELSE ' if UPDATE(['+name+']) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, '+@keycollist+'DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), '''+@TbName+''', '+@inskeycolvalue+'1, '''+name+''', d.['+name+'], i.['+name+'], @DoProc, @DoUserID from inserted i inner join deleted d on '+@joinkeycolvalue+' ISNULL(i.['+name+'],0) <> ISNULL(d.['+name+'],0) END ' END from sys.columns where object_id= object_id(@TbName) AND ','+@colstr+',' like '%,'+name+',%' UNION ALL select ' END ELSE IF @DelCnt = 0 and @InsCnt > 0 --插入新增日志 BEGIN INSERT INTO dbo.EditLog(DbName, TbName, '+@keycollist+'DoType, DoWhere, DoUserID) SELECT Db_Name(),'''+@TbName+''', '+@keycolvalue+'0, @DoProc, @DoUserID from inserted END ELSE IF @DelCnt > 0 and @InsCnt = 0 --插入删除日志 BEGIN INSERT INTO dbo.EditLog(DbName, TbName, '+@keycollist+'DoType, DoWhere, DoUserID) SELECT Db_Name(), '''+@TbName+''', '+@keycolvalue+'2, @DoProc, @DoUserID from deleted INSERT INTO dbo.DelLog(DbName, TbName, DoWhere, DoUserID'+@DelColStr+') SELECT Db_Name(), '''+@TbName+''', @DoProc, @DoUserID'+@DelColValue+' from deleted END END GO ' IF ISNULL(@TbNameCh,'')<>'' BEGIN SELECT 'insert into TbList(DbName,TbName, '+@keycollist+'Descript) select Db_Name(), '''+@TbName+''', '+@keycolname+''''+@TbNameCh+'''' SELECT 'insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, '''+a.col+''', '''+b.col+''',1,'''+ type_name(c.user_type_id)+ CASE WHEN type_name(c.user_type_id) IN('char','varchar','varbinary','binary') THEN '('+case when c.max_length = -1 then 'max' ELSE CAST(c.max_length as varchar(10)) END +')' WHEN type_name(c.user_type_id) IN('nchar','nvarchar') THEN '('+case when c.max_length = -1 then 'max' ELSE CAST(c.max_length/2 as varchar(10)) END +')' ELSE '' END +''' FROM TbList WHERE DbName = Db_Name() AND TbName = '''+@TbName+''' ' from master.dbo.fn_split(@colstr,',') a INNER JOIN master.dbo.fn_split(@colstrCh,',') b on a.pos = b.pos INNER JOIN sys.columns c ON c.object_id= object_id(@TbName) AND c.name = a.col END END GO USE master;EXEC sp_MS_marksystemobject 'dbo.sp_GetUpdateStr';
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON go set ANSI_WARNINGS OFF GO /* -- Author: DBA谭 -- Create date: -- Description: 通用查询 -- Memo: */ CREATE PROC LogQuery @DbName varchar(50), @TbName varchar(50), @Item varchar(50), @value varchar(50), @bgntime datetime, @endtime datetime, @DoType int = -1--（0新增，2删除，1修改，-1不限） AS BEGIN SET NOCOUNT ON declare @KeyColCnt int, @KeyCol varchar(50), @KeyCol2 varchar(50), @KeyCol3 varchar(50), @DbTbName varchar(50), @TbID VARCHAR(10), @ColType varchar(50), @DelLog VARCHAR(8000), @SQL NVARCHAR(MAX) select @TbID = tl.TbID, @KeyCol = tl.KeyCol, @KeyCol2 = tl.KeyCol2, @KeyCol3 = tl.KeyCol3, @ColType = ISNULL(tc.ColType, 'int') from TbList tl LEFT join TbCol tc on tl.TbID = tc.TbID and tc.ColName = @Item WHERE tl.DbName = @DbName and tl.TbName = @TbName SET @DbTbName = '['+@DbName+']..'+'['+@TbName+']' --插入列信息 CREATE TABLE #DelColList(ColName varchar(50), ColNoName varchar(50)) SET @SQL = ' INSERT INTO #DelColList(ColName, ColNoName) select name, ''Col''+CAST(column_id AS VARCHAR(10)) from '+@DbName+'.sys.columns where object_id= object_id('''+@DbTbName+''') ' EXEC(@SQL) select @DelLog = ' ;WITH CET AS ( select '+STUFF(MASTER.dbo.fn_GetSumStr(','+ColNoName+' AS '+ColName),1,1,'')+' from DelLog WHERE DbName = '''+@DbName+''' and TbName = '''+@TbName+''' )' from #DelColList create table #T(keycol sql_variant,keycol2 sql_variant,keycol3 sql_variant) CREATE UNIQUE INDEX idx_unique on #T(keycol,keycol2,keycol3)with(ignore_dup_key = on) begin begin SET @SQL = @SQL + ' insert into #T(keycol,keycol2,keycol3) select dt.['+@KeyCol+'],' +case when @KeyCol2 is NULL then 'NULL'ELSE'dt.[' END +ISNULL(@KeyCol2,'') +case when @KeyCol2 is NULL then ''ELSE']' END +', ' +case when @KeyCol3 is NULL then 'NULL'ELSE'dt.[' END +ISNULL(@KeyCol3,'') +case when @KeyCol3 is NULL then ''ELSE']' END +' from ['+@DbName+']..['+@TbName+'] dt where dt.['+@Item+'] = CAST('''+@value+''' AS '+@ColType+') ' +@DelLog+' insert into #T(keycol,keycol2,keycol3) select dt.['+@KeyCol+'],' +case when @KeyCol2 is NULL then 'NULL'ELSE'dt.[' END +ISNULL(@KeyCol2,'') +case when @KeyCol2 is NULL then ''ELSE']' END +', ' +case when @KeyCol3 is NULL then 'NULL'ELSE'dt.[' END +ISNULL(@KeyCol3,'') +case when @KeyCol3 is NULL then ''ELSE']' END +' from CET dt where dt.['+@Item+'] = CAST('''+@value+''' AS '+@ColType+') ' SET @SQL = @SQL + ' insert into #T(keycol,keycol2,keycol3) select DISTINCT KeyCol, KeyCol2, KeyCol3 from EditLog where DbName = '''+@DbName+''' and TbName = '''+@TbName+''' and ColName = '''+@Item+''' and ( CAST(OldValue AS '+@ColType+') = CAST('''+@value+''' AS '+@ColType+') or CAST(NewValue AS '+@ColType+') = CAST('''+@value+''' AS '+@ColType+') ) ' END IF @Item IN(@KeyCol,@KeyCol2,@KeyCol3) BEGIN SET @SQL = @SQL + ' insert into #T(keycol,keycol2,keycol3) select DISTINCT KeyCol, KeyCol2, KeyCol3 from EditLog where DbName = '''+@DbName+''' and TbName = '''+@TbName+''' and CAST(cast('''+@value+''' as '+@ColType+') AS sql_variant) IN(KeyCol,KeyCol2,KeyCol3) ' END end print @SQL EXEC SP_EXECUTESQL @SQL begin SET @SQL = ' select e.KeyCol,e.KeyCol2,e.KeyCol3, CASE e.DoType WHEN 0 THEN ''新增'' WHEN 1 THEN ''修改'' WHEN 2 THEN ''删除'' END DoType, ISNULL(tc.descript,e.ColName)ColName,OldValue,NewValue,--e.DoUserID, e.Douserid,e.CrtDate from EditLog e inner join #T t on e.keycol = t.keycol '+ CASE WHEN @KeyCol2 is NOT NULL THEN 'AND e.keycol2 = t.keycol2 ' ELSE '' END + CASE WHEN @KeyCol3 is NOT NULL THEN 'AND e.keycol3 = t.keycol3 ' ELSE '' END +' and e.DbName = '''+@DbName+''' and e.TbName = '''+@TbName+''' LEFT JOIN TbCol tc on tc.TbID = '+@TbID+' AND tc.ColName = e.ColName where e.CrtDate between @bgntime and @endtime '+CASE WHEN isnull(@DoType,-1) = -1 THEN '' ELSE ' and e.DoType = @Dotype ' end+' ORDER BY e.KeyCol, e.CrtDate ' end print @SQL EXEC SP_EXECUTESQL @SQL,N'@bgntime datetime, @endtime datetime,@Dotype int', @bgntime = @bgntime,@endtime = @endtime,@Dotype = @Dotype DROP TABLE #T END GO
/* -- Author: Master谭 -- Create date: -- Description: 专用查询 -- Memo: */ ALTER PROC VarLogQuery @DbName varchar(50), @TbName varchar(50), @Item varchar(50), @value varchar(50), @bgntime datetime, @endtime datetime, @DoType int = -1--（0新增，2删除，1修改，-1不限） AS BEGIN SET NOCOUNT ON; CREATE TABLE #T( k1 sql_variant, k2 sql_variant, k3 sql_variant,DoType varchar(10) , ColName varchar(10), Oldvalue sql_variant, NewValue sql_variant, DoName varchar(50), CrtDate datetime) insert into #T exec LogQuery @DbName = @DbName, @TbName = @TbName, @Item = @Item, @value = @value, @bgntime = @bgntime, @endtime = @endtime, @DoType = @DoType--（0新增，2删除，1修改，-1不限） SELECT * FROM #T IF @DbName = 'Northwind' AND @TbName = 'Customers' BEGIN ;with cet as ( select CAST(k1 as nchar(10)) 客户ID, DoType 操作类型, ColName, DoName 操作人, ISNULL(CAST(Oldvalue AS VARCHAR(200)),'') + ' --> ' + ISNULL(CAST(NewValue AS VARCHAR(200)),'') Value, CONVERT(varchar(19),crtdate,120 ) 操作时间 FROM #T ) select p.客户ID, p.操作类型, p.操作人, p.操作时间, isnull(p.[公司],c.CompanyName) 公司, isnull(p.[姓名],c.ContactName) 姓名, isnull(p.[地址],c.Address) 地址, isnull(p.[城市],c.City) 城市, isnull(p.[邮编],c.PostalCode) 邮编, isnull(p.[国家],c.Country) 国家, isnull(p.[电话],c.Phone) 电话, isnull(p.[传真],c.Fax) 传真 FROM cet c pivot(MAX(Value) for ColName in([公司],[姓名],[地址],[城市],[邮编],[国家],[电话],[传真]))p left join Northwind..Customers c on p.客户ID = c.CustomerID END END GO
该例子中自动生成的触发器脚本
/* USE [Northwind] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -- Author: DBA谭 -- Create date: -- Description: -- Memo: */ CREATE TRIGGER trg_Customers ON [dbo].[Customers] for INSERT, UPDATE, DELETE AS BEGIN if @@RowCount <= 0 Return set nocount on declare @DelCnt int, @InsCnt int DECLARE @DoUserID int, @DoProc varchar(50) --读取上下文信息 EXEC dbo.GetDoInfo @DoUserID OUTPUT, @DoProc OUTPUT SELECT @DelCnt = COUNT(1) FROM deleted SELECT @InsCnt = COUNT(1) FROM inserted IF @DelCnt > 0 and @InsCnt > 0 BEGIN --插入修改日志 if UPDATE([CustomerID]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'CustomerID', d.[CustomerID], i.[CustomerID], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[CustomerID],0) <> ISNULL(d.[CustomerID],0) END if UPDATE([CompanyName]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'CompanyName', d.[CompanyName], i.[CompanyName], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[CompanyName],0) <> ISNULL(d.[CompanyName],0) END if UPDATE([ContactName]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'ContactName', d.[ContactName], i.[ContactName], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[ContactName],0) <> ISNULL(d.[ContactName],0) END if UPDATE([Address]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'Address', d.[Address], i.[Address], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[Address],0) <> ISNULL(d.[Address],0) END if UPDATE([City]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'City', d.[City], i.[City], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[City],0) <> ISNULL(d.[City],0) END if UPDATE([PostalCode]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'PostalCode', d.[PostalCode], i.[PostalCode], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[PostalCode],0) <> ISNULL(d.[PostalCode],0) END if UPDATE([Country]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'Country', d.[Country], i.[Country], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[Country],0) <> ISNULL(d.[Country],0) END if UPDATE([Phone]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'Phone', d.[Phone], i.[Phone], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[Phone],0) <> ISNULL(d.[Phone],0) END if UPDATE([Fax]) BEGIN INSERT INTO dbo.EditLog(DbName,TbName, [keycol], DoType, ColName, OldValue, NewValue, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', i.[CustomerID], 1, 'Fax', d.[Fax], i.[Fax], @DoProc, @DoUserID from inserted i inner join deleted d on i.[CustomerID] = d.[CustomerID] and ISNULL(i.[Fax],0) <> ISNULL(d.[Fax],0) END END ELSE IF @DelCnt = 0 and @InsCnt > 0 --插入新增日志 BEGIN INSERT INTO dbo.EditLog(DbName, TbName, [keycol], DoType, DoWhere, DoUserID) SELECT Db_Name(),'Customers', [CustomerID], 0, @DoProc, @DoUserID from inserted END ELSE IF @DelCnt > 0 and @InsCnt = 0 --插入删除日志 BEGIN INSERT INTO dbo.EditLog(DbName, TbName, [keycol], DoType, DoWhere, DoUserID) SELECT Db_Name(), 'Customers', [CustomerID], 2, @DoProc, @DoUserID from deleted INSERT INTO dbo.DelLog(DbName, TbName, DoWhere, DoUserID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11) SELECT Db_Name(), 'Customers', @DoProc, @DoUserID, [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted END END GO insert into TbList(DbName,TbName, [keycol], Descript) select Db_Name(), 'Customers', 'CustomerID', '客户表' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'CustomerID', '客户ID',1,'nchar(5)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'CompanyName', '公司',1,'nvarchar(40)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'ContactName', '姓名',1,'nvarchar(30)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'Address', '地址',1,'nvarchar(60)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'City', '城市',1,'nvarchar(15)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'PostalCode', '邮编',1,'nvarchar(10)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'Country', '国家',1,'nvarchar(15)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'Phone', '电话',1,'nvarchar(24)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' insert into TbCol(TbID, ColName, Descript, IsFilter,ColType) select TbID, 'Fax', '传真',1,'nvarchar(24)' FROM TbList WHERE DbName = Db_Name() AND TbName = 'Customers' */
