mssql记录某些重要的表，update记录原值，新值，更改时间的方法

zjl8008 2020-06-06 04:29:18

可以用update触发器，但我自己写的方法不够灵活，在表结构列有增加时，还得修改触发器。有时还莫名报错，XXX（新加）列无效。
求比较通用的触发器写法？
附我写的一个表的记录方法



CREATE TABLE [dbo].[bl_jbzl_xgjl](

	[id] [bigint] IDENTITY(1,1) NOT NULL,

	[grbm] [varchar](30) NULL,

	[zdm] [varchar](30) NULL,

	[yz] [varchar](max) NULL,

	[xz] [varchar](max) NULL,

	[czrq] [datetime] NOT NULL,

	[czr] [varchar](30) NULL,

	[remark] [varchar](200) NULL,

	[by1] [varchar](80) NULL,

	[by2] [varchar](80) NULL,

	[by3] [varchar](80) NULL,

	[by4] [varchar](80) NULL,

	[by5] [varchar](80) NULL,

	[Column_14] [char](10) NULL,

 CONSTRAINT [PK_bl_jbzl_xgjl] PRIMARY KEY CLUSTERED 

(

	[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



GO



CREATE TABLE [dbo].[BL_jbzl](

	[grbm] [varchar](50) NOT NULL,

	[jkdam] [varchar](50) NULL,

	[xm] [varchar](80) NULL,

	[xb] [varchar](50) NULL,

	[xmpym] [varchar](50) NULL,

	[csrq] [datetime] NULL,

	[hyzk] [varchar](50) NULL,

	[zy] [varchar](50) NULL,

	[csd] [varchar](50) NULL,

	[mz] [varchar](50) NULL,

	[gj] [varchar](50) NULL,

	[sfzh] [varchar](50) NULL,

	[gzdw] [varchar](100) NULL,

	[dwdh] [varchar](50) NULL,

	[dwyb] [varchar](50) NULL,

	[hkdz] [varchar](200) NULL,

	[hkyb] [varchar](50) NULL,

	[yblx] [varchar](50) NULL,

	[ybkh] [varchar](50) NULL,

	[lrr] [varchar](50) NULL,

	[lrrq] [datetime] NULL,

	[bz] [varchar](max) NULL,

	[by1] [varchar](200) NULL,

	[by2] [varchar](200) NULL,

	[by3] [varchar](200) NULL,

	[by4] [varchar](200) NULL,

	[by5] [varchar](200) NULL,

	[by6] [datetime] NULL,

	[pym] [varchar](50) NULL,

	[wbm] [varchar](20) NULL,

	[dcbz] [int] NULL CONSTRAINT [DF_bl_jbzl_dcbz]  DEFAULT ((0)),

	[dcrq] [datetime] NULL,

	[dcr] [int] NULL,

	[csd_mx] [varchar](100) NULL,

	[jiguan] [varchar](100) NULL,

	[xclxrq] [datetime] NULL,

	[zhlxrq] [datetime] NULL,

	[khdj] [varchar](20) NULL,

	[khly] [varchar](20) NULL,

	[khzt] [varchar](50) NULL,

	[khxiny] [varchar](20) NULL,

	[sfhf] [tinyint] NULL CONSTRAINT [DF_BL_jbzl_sfhf]  DEFAULT ((1)),

	[khhklx] [varchar](50) NULL,

	[khbingc] [varchar](200) NULL,

	[khfp] [varchar](50) NULL,

	[khfbrq] [datetime] NULL,

	[jdnl] [varchar](20) NULL,

	[ybgrbh] [varchar](30) NULL,

	[ybdwbh] [varchar](30) NULL,

	[bntcljzc] [money] NULL,

	[whcd] [varchar](20) NULL,

	[jkkh_tmp] [varchar](20) NULL,

	[AAE073] [decimal](15, 0) NULL,

	[tcljzf] [money] NULL,

	[yblb] [varchar](30) NULL,

	[AAC903] [varchar](1) NULL DEFAULT ((0)),

	[lxrsfzh] [varchar](20) NULL,

	[yb_qhdm] [varchar](50) NULL,

	[ye] [money] NULL,

	[xgr] [varchar](50) NULL,

	[xgrq] [datetime] NULL,

	[qcqfbzlj] [money] NULL,

	[tsbksrq] [varchar](50) NULL,

	[tsbjsrq] [varchar](50) NULL,

	[pkrkwxyj] [tinyint] NULL,

	[mobble] [varchar](50) NULL,

	[iszz] [tinyint] NULL,

	[tlmzbl] [money] NULL,

	[tlzybl] [money] NULL,

	[guardIdType] [varchar](10) NULL,

	[patType] [varchar](10) NULL,

 CONSTRAINT [PK_BL_jbzl] PRIMARY KEY CLUSTERED 

(

	[grbm] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



GO

go



create TRIGGER [dbo].[Tri_bl_jbzl_update]

   ON  [dbo].[BL_jbzl] 

   for UPDATE

AS 

BEGIN

	declare @ziduan varchar(10)

	declare @sql varchar(8000)



	select * into #ins from Inserted 

	select * into #del from Deleted



	declare cursor1 cursor for

		select [name] from syscolumns where id=(select id from sysobjects where type='u' and name='bl_jbzl' ) and [name] not in('xgr','xgrq')

	open cursor1                       --打开游标

	fetch next from cursor1 into @ziduan --将游标向下移1

	while @@fetch_status=0           --判断是否成功获取数据

	begin

		set @sql='insert into bl_jbzl_xgjl(grbm,zdm,yz,xz,czr,czrq) select a.grbm,'''+@ziduan+''',convert(varchar(2000),b.'+@ziduan+'),convert(varchar(2000),a.'+@ziduan+'),a.xgr,getdate() from #ins a,#del b where a.grbm=b.grbm and ((a.'+@ziduan+'<>b.'+@ziduan+') or (a.'+@ziduan+' is null and b.'+@ziduan+' is not null) or (a.'+@ziduan+' is not null and b.'+@ziduan+' is null))'

		exec (@sql)

		fetch next from cursor1 into @ziduan 

	end

	close cursor1                   --关闭游标

	deallocate cursor1 

END