在ps_djhz和ps_djmx兩表中建新增觸發器:
...
update C
set C.beizhu=case when A.danjbh=1 then 'aa' when A.danjbh=2 then 'bb' end
from ps_djhz A
inner join ps_djmx B on B.danjbh=A.danjbh --兩表相同才更改
inner join sp_kfpc C on C.dspid=B.dspid
...
...
update C
set C.beizhu=case when A.danjbh=1 then 'aa' when A.danjbh=2 then 'bb' end
from ps_djhz A
inner join ps_djmx B on B.danjbh=A.danjbh --兩表相同才更改
inner join sp_kfpc C on C.dspid=B.dspid
...
a.触发器是在新增是才处理,这点确定吧?
b.ps_djhz,ps_djmx 表中,有主键吗?
c.sp_kfpc 表中,原本就有记录吗?
ps_djhz,ps_djmx的主键都为danjbh
sp_kfpc里原本有记录,只不过我想修改备注字段为sp_djhz里的对应beizhu字段。回答邹大哥:
如果我在ps_djmx 上加触发器,当它有insert 时,不就可以通过danjbh和dspid,picih,dkfid同时分别关联ps_djhz和sp_kfpc吗?
create trigger tr_insert on ps_djmx
for insert
as
update sp_kfpc set beizhu=c.beizhu
from sp_kfpc a
join inserted b on a.dspid=b.dspid
and a.picih=b.picih and a.dkfid=b.dkfid
join ps_djhz c on b.danjbh=c.danjbh
go
create table ps_djhz(danjbh int,beizhu varchar(10))
insert ps_djhz select 1,'aa'
union all select 2,'bb'create table ps_djmx(danjbh int,dspid char(3),picih int,dkfid char(3))create table sp_kfpc(dspid char(3),picih int,dkfid char(3),beizhu varchar(10))
insert sp_kfpc(dspid,picih,dkfid)
select '001',11,'001'
union all select '001',22,'001'
union all select '002',11,'001'
union all select '003',11,'002'
go--处理的触发器
create trigger tr_insert on ps_djmx
for insert
as
update sp_kfpc set beizhu=c.beizhu
from sp_kfpc a
join inserted b on a.dspid=b.dspid
and a.picih=b.picih and a.dkfid=b.dkfid
join ps_djhz c on b.danjbh=c.danjbh
go--插入记录
insert ps_djmx select 1,'001',11,'001'
union all select 1,'002',11,'001'
union all select 2,'001',22,'001'
union all select 2,'003',11,'002'
go--显示处理的结果
select * from ps_djmx
select * from sp_kfpc
go--删除测试
drop table ps_djhz,ps_djmx,sp_kfpc/*--测试结果danjbh dspid picih dkfid
----------- ----- ----------- -----
1 001 11 001
1 002 11 001
2 001 22 001
2 003 11 002(所影响的行数为 4 行)
dspid picih dkfid beizhu
----- ----------- ----- ----------
001 11 001 aa
001 22 001 bb
002 11 001 aa
003 11 002 bb(所影响的行数为 4 行)--*/
不知道怎么给,TMD
sp_kfpc里的beizhu 就是不变啊
create table ps_djhz(danjbh int,beizhu varchar(10))
insert ps_djhz select 1,'aa'
union all select 2,'bb'create table ps_djmx(danjbh int,dspid char(3),picih int,dkfid char(3))create table sp_kfpc(dspid char(3),picih int,dkfid char(3),beizhu varchar(10))
insert sp_kfpc(dspid,picih,dkfid)
select '001',11,'001'
union all select '001',22,'001'
union all select '002',11,'001'
union all select '003',11,'002'
go--处理的触发器
create trigger tr_insert on ps_djmx
for insert
as
update sp_kfpc set beizhu=c.beizhu
from sp_kfpc a
join inserted b on a.dspid=b.dspid
and a.picih=b.picih and a.dkfid=b.dkfid
join ps_djhz c on b.danjbh=c.danjbh
go--逐条插入记录
insert ps_djmx select 1,'001',11,'001'
insert ps_djmx select 1,'002',11,'001'
insert ps_djmx select 2,'001',22,'001'
insert ps_djmx select 2,'003',11,'002'
go--显示处理的结果
select * from ps_djmx
select * from sp_kfpc
go--删除测试
drop table ps_djhz,ps_djmx,sp_kfpc/*--测试结果danjbh dspid picih dkfid
----------- ----- ----------- -----
1 001 11 001
1 002 11 001
2 001 22 001
2 003 11 002(所影响的行数为 4 行)
dspid picih dkfid beizhu
----- ----------- ----- ----------
001 11 001 aa
001 22 001 bb
002 11 001 aa
003 11 002 bb(所影响的行数为 4 行)--*/
给sp_kfpc里的beizhu了?
create trigger tr_insert on ps_djhz
for insert
as
update sp_kfpc set beizhu=c.beizhu
from sp_kfpc a
join ps_djmx b on a.dspid=b.dspid
and a.picih=b.picih and a.dkfid=b.dkfid
join inserted c on b.danjbh=c.danjbh
go
这次简单过头了吧,这么快就结了!