在学生基本信息表XSJBXXB中建立一个触发器tri_XM,该触发器的作用：当学生基本信息表中某位学生的姓名发生了变化，则自动修改学生等级考试成绩表（Xsdjksb）和成绩表（CJB）中该生的姓名（该学号对应的姓名）。

用update触发器结果显示不行

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
求指导哪里出错了

--建立数据
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编辑过]

--有个缺陷,只能更新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