现有的触发器为:
触发器:现在有的内容:
ALTER TRIGGER "tr_act_recv_line_in" after insert order 2 on
dba.act_recv_line
referencing new as new_act_recv_line
for each row
begin
update act_recv_line set
create_time = getdate(*) where
act_recv_line.trans_no = new_act_recv_line.trans_no and
act_recv_line.line_no = new_act_recv_line.line_no
end想在触发器插入前执行查询此客户此产品的折扣是多少并且直接运算他的折扣
shipper_no part_id ar_amt goods_amt disc_amt user_9(改为优惠率) sys_ar_amt sys_goods_amt
s2013001 a001 user_9 = ( 1- DI表里面的discount)1-0.82=0.18 di表里面需要判断此客户先然后再
disc_amt = ( goods_amt*user_9)
ar_amt,sys_ar_amt=goods_amt-disc_amt
SELECT discount_detail.discount,shipper.customer_id,act_recv_line.part_id FROM DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper WHERE act_recv_line.shipper_no=shipper.trans_no and
shipper.customer_id=discount_relation.customer_id and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iDUser_9 =discount_detail.discount 如果没有查询那么user_9为1(即无折扣
触发器
触发器:现在有的内容:
ALTER TRIGGER "tr_act_recv_line_in" after insert order 2 on
dba.act_recv_line
referencing new as new_act_recv_line
for each row
begin
update act_recv_line set
create_time = getdate(*) where
act_recv_line.trans_no = new_act_recv_line.trans_no and
act_recv_line.line_no = new_act_recv_line.line_no
end想在触发器插入前执行查询此客户此产品的折扣是多少并且直接运算他的折扣
shipper_no part_id ar_amt goods_amt disc_amt user_9(改为优惠率) sys_ar_amt sys_goods_amt
s2013001 a001 user_9 = ( 1- DI表里面的discount)1-0.82=0.18 di表里面需要判断此客户先然后再
disc_amt = ( goods_amt*user_9)
ar_amt,sys_ar_amt=goods_amt-disc_amt
SELECT discount_detail.discount,shipper.customer_id,act_recv_line.part_id FROM DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper WHERE act_recv_line.shipper_no=shipper.trans_no and
shipper.customer_id=discount_relation.customer_id and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iDUser_9 =discount_detail.discount 如果没有查询那么user_9为1(即无折扣
触发器
本帖最后由 mfkpie8 于 2013-5-17 13:12 编辑
触发器:现在有的内容:
ALTER TRIGGER "tr_act_recv_line_in" after insert order 2 on
dba.act_recv_line
referencing new as new_act_recv_line
for each row
begin
update act_recv_line set
create_time = getdate(*) where
act_recv_line.trans_no = new_act_recv_line.trans_no and
act_recv_line.line_no = new_act_recv_line.line_no
end——————————————————————————————————————
Di表
part_id discount d_no
a001 0.82 0001
a001 0.7 0002
a001 0.5 0003
————————————————————————————————————————
d表
d_no customer_id
0001 客户2
0002 客户1
0003 客户6
—————————————————————————————————————————
sh表
trans_no customer_id
s2013001 客户2
s2013009 客户9
——————————————————————————————————————————
触发器:想在触发器插入前执行查询此客户此产品的折扣是多少并且直接运算他的折扣shipper_no part_id ar_amt goods_amt disc_amt user_9(改为优惠率) sys_ar_amt* sys_goods_amt
s2013001 a001 100
s2013009 b002 900 做个判断:如果没有shipper_no 那么查询就不去运算,查询出没有折扣的那么就USER_9=1(1一是无折扣)
user_9 = (1- DI表里面的discount) 0.82 di表里面需要判断此客户先然后再
disc_amt = ( goods_amt*user_9)
ar_amt =goods_amt-disc_amt
sys_ar_amt =goods_amt-disc_amt那么 客户2 的结果就为:
shipper_no part_id ar_amt goods_amt disc_amt user_9(改为优惠率) sys_ar_amt* sys_goods_amt
s2013001 a001 92 100 18 0.18 92 100那么 客户9 的结果为:
shipper_no part_id ar_amt goods_amt disc_amt user_9(改为优惠率) sys_ar_amt* sys_goods_amt
s2013009 b002 100 100 0 1 100 100
这个我是用SQL查询的是可以的
SELECT discount_detail.discount,shipper.customer_id,act_recv_line.part_id FROM DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper WHERE act_recv_line.shipper_no=shipper.trans_no and
shipper.customer_id=discount_relation.customer_id and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iDUser_9 =discount_detail.discount 如果没有查询那么user_9为1(即无折扣