MSSQL record some important tables, update record original value and new value, the method of change-CodePudding

Can be used to update the trigger, but I write my own method of flexible enough, listed in table structure has increased, still have to modify the trigger, sometimes difficult to express an error, XXX (new) column is null and void,
O trigger more commonly written?
With the method of a table I wrote

 
The CREATE TABLE [dbo] [bl_jbzl_xgjl] (
[id]/bigint IDENTITY (1, 1) NOT NULL, 
[GRBM] [varchar] (30) NULL, 
[ZDM] [varchar] (30) NULL, 
[z] [varchar] (Max) is NULL, 
[xz] [varchar] (Max) is 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, 
PRIMARY KEY CONSTRAINT [PK_bl_jbzl_xgjl] 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 the [PRIMARY] TEXTIMAGE_ON (PRIMARY) 

GO 

The 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) is 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] a decimal (15, 0) NULL, 
[TCLJZF] [money] NULL, 
[yblb] [varchar] (30) NULL, 
[AAC903] [varchar] (1) the 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, 
PRIMARY KEY CONSTRAINT [PK_BL_jbzl] 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 the [PRIMARY] TEXTIMAGE_ON (PRIMARY) 

GO 
Go 

Create the TRIGGER [dbo] [Tri_bl_jbzl_update] 
ON [dbo] [BL_jbzl] 
For UPDATE 
AS 
The BEGIN 
Declare @ ziduan varchar (10) 
Declare @ SQL varchar (8000) 

Select * into # ins from Inserted 
Select * # into del from does 

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) 
The open cursor1 - open the cursor 
The fetch next from cursor1 into @ ziduan - the cursor down 1 
While @ @ fetch_status=0 - determine whether success to get the data 
The begin 
Set @ SQL="insert into bl_jbzl_xgjl (CZR GRBM ZDM, yz, xz, CZRQ) select al-qeada matter, ' '+ @ ziduan +"', the convert (varchar (2000), b. '+ @ ziduan +'), the convert (varchar (2000), a. '+ @ ziduan +'), a.x gr, getdate () from # ins a, # del b where al-qeada matter=b.g matter and ((a. '+ @ ziduan + & lt;> B. '+ @ ziduan +) or (a.' + @ ziduan + is null and b. '+ @ ziduan +' is not null) or (a. '+ @ ziduan +' is not null and b. '+ @ ziduan + is null)) 
'The exec (@ SQL) 
The fetch next from cursor1 into @ ziduan 
End 
The close cursor1 - close the cursor 
Deallocate cursor1 
END

CodePudding user response:

Reference: treasure key columns are documented,
https://blog.csdn.net/yole_grise/article/details/50156335