Create table a (FID int,FName varchar(80),FCheckerID int)
Create table a1(FID int,FEntryID int,FQty int,FSrcID int,FSrcEntryID int)
Create table b (FID int,FName varchar(80))
Create table b1(FID int,FEntryID int,FQty int)Insert into a values(1,'单据1',0)
Insert into a1 values(1,1,10,1,2)
Insert into a1 values(1,2,6,1,3)Insert into b values(1,'单据1')
Insert into b1 values(1,1,10)
Insert into b1 values(1,2,5)
Insert into b1 values(1,3,7)select a1.FQty from a left join a1 on a.FID=a1.FIDDrop table a
Drop table a1
Drop table b
Drop table b1a表为a1表的父表,b表为b1表的父表,表a1的FSrcID和FSrcEntryID的值对应表b1的FID、FEntryID
现在需要在a表写个触发器当表a的字段FCheckerID不为0时,则将表a1的FQty反写到与b1表对应的字段
即表b1更新后的值为FID ,FEntryID ,FQty
1 1 10
1 2 10
1 3 6触发器写在表a上,这个触发器这么写啊???
Create table a1(FID int,FEntryID int,FQty int,FSrcID int,FSrcEntryID int)
Create table b (FID int,FName varchar(80))
Create table b1(FID int,FEntryID int,FQty int)Insert into a values(1,'单据1',0)
Insert into a1 values(1,1,10,1,2)
Insert into a1 values(1,2,6,1,3)Insert into b values(1,'单据1')
Insert into b1 values(1,1,10)
Insert into b1 values(1,2,5)
Insert into b1 values(1,3,7)select a1.FQty from a left join a1 on a.FID=a1.FIDDrop table a
Drop table a1
Drop table b
Drop table b1a表为a1表的父表,b表为b1表的父表,表a1的FSrcID和FSrcEntryID的值对应表b1的FID、FEntryID
现在需要在a表写个触发器当表a的字段FCheckerID不为0时,则将表a1的FQty反写到与b1表对应的字段
即表b1更新后的值为FID ,FEntryID ,FQty
1 1 10
1 2 10
1 3 6触发器写在表a上,这个触发器这么写啊???
Create table a1(FID int,FEntryID int,FQty int,FSrcID int,FSrcEntryID int)
Create table b (FID int,FName varchar(80))
Create table b1(FID int,FEntryID int,FQty int) --Insert into a values(1,'单据1',0)
Insert into a1 values(1,1,10,1,2)
Insert into a1 values(1,2,6,1,3) Insert into b values(1,'单据1')
Insert into b1 values(1,1,10)
Insert into b1 values(1,2,5)
Insert into b1 values(1,3,7)
gocreate trigger trig_ins
on a
for insert
as
begin
-- if exists(select 1 from inserted where FCheckerID <> 0)
update b
set FQty = isnull(a.FQty,b.fqty)
from b1 b
left join a1 a on a.FSrcID = b.FID and a.FSrcEntryID=b.FEntryID
left join inserted i on a.fid = i.fid
endgoInsert into a values(1,'单据1',0) select * from b1Drop table a
Drop table a1
Drop table b
Drop table b1
/*FID FEntryID FQty
----------- ----------- -----------
1 1 10
1 2 10
1 3 6(所影响的行数为 3 行)
*/
Create table a1(FID int,FEntryID int,FQty int,FSrcID int,FSrcEntryID int)
Create table b (FID int,FName varchar(80))
Create table b1(FID int,FEntryID int,FQty int) Insert into a values(1,'单据1',0)
Insert into a1 values(1,1,10,1,2)
Insert into a1 values(1,2,6,1,3) Insert into b values(1,'单据1')
Insert into b1 values(1,1,10)
Insert into b1 values(1,2,5)
Insert into b1 values(1,3,7)
gocreate trigger trig_ins
on a
for insert
as
begin
if exists(select 1 from inserted where FCheckerID <> 0)
update b
set FQty = isnull(a.FQty,b.fqty)
from b1 b
left join a1 a on a.FSrcID = b.FID and a.FSrcEntryID=b.FEntryID
left join inserted i on a.fid = i.fid
endgo
--test the triggerInsert into a values(1,'单据1',10)
/*FID FEntryID FQty
----------- ----------- -----------
1 1 10
1 2 10
1 3 6(所影响的行数为 3 行)
*/
select * from b1
Drop table a
Drop table a1
Drop table b
Drop table b1
一张单据1由表头和表头数据构成,表A存表头数据表A1存表体数据
另一张单据2的表头数据存在B,表体数据存在表B1里
单据1和单据2关联,关联字段为表A里字段FSrcID和FSrcEntryID
FSrcID存表B里的FID,而FSrcEntryID存表B1的FEntryID.
现在就是要通过这个关联字段反写对应的记录.