我在msdn找到了一個能夠用觸發器更新text字段的方法,經過測試是可用的。drop table SY03900_AUDIT
go
CREATE TABLE [dbo].[SY03900_AUDIT]( [NOTEINDX] [numeric](19, 5) NOT NULL, [DATE1] [datetime] NOT NULL, [TIME1] [datetime] NOT NULL, [TXTFIELD_OLD] [text] NULL, [TXTFIELD_NEW] [text] NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
drop trigger SY03900_U
go
create trigger SY03900_U on SY03900 INSTEAD OF UPDATE as
begin
insert into SY03900_AUDIT (NOTEINDX,DATE1,TIME1,TXTFIELD_OLD,TXTFIELD_NEW)
select b.NOTEINDX,b.DATE1,b.TIME1,a.TXTFIELD,b.TXTFIELD from SY03900 a
inner join inserted b on a.NOTEINDX = b.NOTEINDX
/* Only insert audit record if TXTFIELD actually changed and not the DATE1 or TIME1 field.
Used "like" in where clause because SQL 2008 wouldn't let me use <>.
Seems to work fine in that old value of 'abc' is apparently not "like" 'abcd' and so the insert does happen. */
where a.TXTFIELD not like b.TXTFIELD /* lastly update the SY03900 table with the updated data */
update a set a.TXTFIELD = b.TXTFIELD, a.DATE1 = b.DATE1, a.TIME1 = b.TIME1
from SY03900 a inner join inserted b on a.NOTEINDX = b.NOTEINDXend但是現在有個問題,我想實現這樣的功能:首先我有兩個數據庫,分別有兩張字段相同的表,我想通過instead of update 實現兩個表能夠同時更新,該如何修改才正確?SQL
go
CREATE TABLE [dbo].[SY03900_AUDIT]( [NOTEINDX] [numeric](19, 5) NOT NULL, [DATE1] [datetime] NOT NULL, [TIME1] [datetime] NOT NULL, [TXTFIELD_OLD] [text] NULL, [TXTFIELD_NEW] [text] NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
drop trigger SY03900_U
go
create trigger SY03900_U on SY03900 INSTEAD OF UPDATE as
begin
insert into SY03900_AUDIT (NOTEINDX,DATE1,TIME1,TXTFIELD_OLD,TXTFIELD_NEW)
select b.NOTEINDX,b.DATE1,b.TIME1,a.TXTFIELD,b.TXTFIELD from SY03900 a
inner join inserted b on a.NOTEINDX = b.NOTEINDX
/* Only insert audit record if TXTFIELD actually changed and not the DATE1 or TIME1 field.
Used "like" in where clause because SQL 2008 wouldn't let me use <>.
Seems to work fine in that old value of 'abc' is apparently not "like" 'abcd' and so the insert does happen. */
where a.TXTFIELD not like b.TXTFIELD /* lastly update the SY03900 table with the updated data */
update a set a.TXTFIELD = b.TXTFIELD, a.DATE1 = b.DATE1, a.TIME1 = b.TIME1
from SY03900 a inner join inserted b on a.NOTEINDX = b.NOTEINDXend但是現在有個問題,我想實現這樣的功能:首先我有兩個數據庫,分別有兩張字段相同的表,我想通過instead of update 實現兩個表能夠同時更新,該如何修改才正確?SQL
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货