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 = 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'
*/
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
