有两个表:a表字段,no,itm,price,cost。b表字段,no,itm,up.
需求:当b表中插入一条或多条,更新a表中no和itm对应的字段price=up,cost=up.当b表中的up更新时,也同时更新a表中no和itm对应的字段price=up,cost=up.
需求:当b表中插入一条或多条,更新a表中no和itm对应的字段price=up,cost=up.当b表中的up更新时,也同时更新a表中no和itm对应的字段price=up,cost=up.
会不会同时插入两条no,itm相同的纪录,这时up如何计算,加在一起?还是随便取一个
--第一个insert触发器里这么写
update a set price='up',cost='up' from a,inserted b
where a.no=b.no and a.itm=b.itm
--第二个的话,update触发器要对应到更新具体哪个字段的,你要更新哪个字段然后触发?
--当然只是触发条件要你自己判断下选取,写法还是如下:
update a set price='up',cost='up' from a,inserted b
where a.no=b.no and a.itm=b.itm
--因为update实际相当于先delete再insert
--固从inserted表里取no,itm,其实从deleted表里取也可以
on b
for insert,update
asupdate a set
price= t.up
,cost = t.up
from a,(
select no,itm
,sum(up) as up -- 或者max(up) as up
from inserted
group by no,itm
) as t
where a.no = t.no
and a.itm = t.itminsert a(no,itm,price,cost)
select no,itm
,sum(up) as up -- 或者max(up) as up
,sum(up) as up -- 或者max(up) as up
from inserted i
where not exists (
select 1
from a
where a.no =i.no
and a.itm = i.itm
)
group by no,itmgo
红色的部分是怎么更新的??
--建表
create table tb6
(
no int primary key,
itm varchar(20),
price int,
cost int
)
create table tb7
(
no int primary key,
itm varchar(20),
up int
)
--建立触发器
create trigger insert_Trigger
on tb7
after insert,update
as
update tb6 set price=tb7.up,cost=tb7.up
from tb7,tb6
where tb6.no=tb7.no and tb6.itm=tb7.itm
--测试数据
insert tb6 values(100,'test1',20,30)
insert tb7 values(100,'test1',40)
update tb7 set up=90 where no=100
with f as
(select * from up_def where price_id='3' and e_dd>=getdate())
select * from f t where not exists(select 1 from f where prd_no=t.prd_no and sys_date>t.sys_date)
另外我还想把这个查询结果放到临时表中,也是学人家的,但就是放不进去,帮帮我.
with f as (select * from up_def where price_id='3' and e_dd>=getdate())
select * from f t where not exists(select 1 from f where prd_no=t.prd_no and sys_date>getdate())
我现在是用啊,学的和用的差太多了,学习中