刚学数据库，不会写一道大题，求高手解救！ - 编程论坛

#2

活泼的胆小鬼2016-06-19 17:09

用update触发器结果显示不行

#3

活泼的胆小鬼2016-06-19 17:31

#4

活泼的胆小鬼2016-06-19 17:42

create trigger tri_pz
on XSJBXXB
after update
as
declare @a nvarchar(20)
select @a=xm
from XSJBXXB
where XH=(select XH
from cjb join xsdjksb on cjb.XH = xsdjksb.XH )
update XSJBXXB and cjb and xsdjksb
求指导哪里出错了

#5

mywisdom882016-06-20 11:17

--建立数据
create table xsjbxxb(xh varchar(10),xm nvarchar(20),xb varchar(2))
insert into xsjbxxb(xh,xm,xb) values('2016090001',张三','男')
insert into xsjbxxb(xh,xm,xb) values('2016090002',李四','女')
insert into xsjbxxb(xh,xm,xb) values('2016090003',王五','男')
insert into xsjbxxb(xh,xm,xb) values('2016090004',赵六','男')

create table xsdjksb(xh varchar(10),xm nvarchar(20),dj varchar(10))
insert into xsdjksb(xh,xm,dj) values('2016090001',张三','计算机一级')
insert into xsdjksb(xh,xm,dj) values('2016090002',李四','计算机二级')
insert into xsdjksb(xh,xm,dj) values('2016090003',王五','会计一级')
insert into xsdjksb(xh,xm,dj) values('2016090004',赵六','会计二级')

create table cjb(xh varchar(10),xm nvarchar(20),cj numeric(6,2))
insert into cjb(xh,xm,cj) values('2016090001',张三',95.50)
insert into cjb(xh,xm,cj) values('2016090002',李四',85.00)
insert into cjb(xh,xm,cj) values('2016090003',王五',96.50)
insert into cjb(xh,xm,cj) values('2016090004',赵六',80.00)
go
--建立触发器,不能和上面部分与下面部分一起运行，要单独运行，我在这里全部写了出来。
create trigger [dbo].[tri_xm]
on xsjbxxb
after update
as
declare @xh1 varchar(10),@xm1 nvarchar(20)
begin
select @xh1=xh,@xm1=xm from inserted
update xsdjksb set xm=@xm1 where xh=@xh1
update cjb set xm=@xm1 where xh=@xh1
end

--测试,SQL2000测试正常，触发更新表xsdjksb，cjb
update xsjbxxb set xm='张叄' where xh='2016090001'
select * from xsjbxxb
select * from xsdjksb
select * from cjb

[此贴子已经被作者于2016-6-20 13:53编辑过]

#6

mywisdom882016-06-20 13:57

回复 5楼 mywisdom88

--有个缺陷,只能更新1个,下面的可以更新N个.
update xsjbxxb set xm=xm+'a' where xh like '%'+'201609000'+'%' --在原来的名称后面+'A'
--触发器改为:
create trigger [dbo].[tri_xm]
on xsjbxxb
after update
as
--declare @xh1 varchar(10),@xm1 nvarchar(20) --去掉
begin
--select @xh1=xh,@xm1=xm from inserted --去掉
update xsdjksb set xm=b.xm from xsdjksb a,inserted b where a.xh=b.xh
update cjb set xm=b.xm from cjb a,inserted b where a.xh=b.xh
end