字段名：
d_id丨d_pur_sum丨d_sal_sum丨d_sal_pur_sum丨d_sal_reb_sum丨d_cost_sum丨d_com_sum丨d_gp丨d_np丨d_npr
其中d_id是主键
触发条件：
1、当d_pur_sum丨d_sal_sum丨d_sal_pur_sum丨d_sal_reb_sum丨d_cost_sum丨d_com_sum 六个字段全部不为空（就是第一次这几个字段全部都有值的时候）
2、当d_pur_sum丨d_sal_sum丨d_sal_pur_sum丨d_sal_reb_sum丨d_cost_sum丨d_com_sum 六个字段全部不为空的时候，其中任意一个字段值发生改变。
3、表中哪一行数据满足触发条件就触发哪一行，不要全表触发。
触发结果：
d_gp = d_sal_sum - d_sal_pur_sum - d_sal_reb_sum - d_com_sum
d_np = d_sal_sum - d_sal_pur_sum - d_sal_reb_sum - d_com_sum - d_cost_sum
d_npr=d_np/d_sal_pur_sum 的一个百分比，保留两位小数。
------解决思路----------------------
create trigger pur_U on 表 for update
as
if exists(select 1 from inserted a join deleted b on a.d_id=b.did
where isnull(a.d_pur_sum,0)>0
and isnull(a.d_sal_sum,0)>0
and isnull(a.d_sal_pur_sum,0)>0
and isnull(a.d_sal_reb_sum,0)>0
and isnull(a.d_cost_sum,0)>0
and isnull(a.d_com_sum,0)>0
and (isnull(b.d_pur_sum,0)=0
or isnull(b.d_sal_sum,0)=0
or isnull(b.d_sal_pur_sum,0)=0
or isnull(b.d_sal_reb_sum,0)=0
or isnull(b.d_cost_sum,0)=0
or isnull(b.d_com_sum,0)=0)
begin
update ...
end
------解决思路----------------------
create trigger insert_table on [表名]
for insert
as
begin
if(exists(select * from inserted where isnull(d_pur_sum,0)>0 and isnull(d_sal_sum,0)>0 and isnull(d_sal_pur_sum,0)>0 and isnull(d_sal_reb_sum,0)>0 and isnull(d_cost_sum,0)>0 and isnull(d_com_sum,0)>0))
begin
update a set
d_gp = isnull(d_sal_sum,0) - isnull(d_sal_pur_sum,0) - isnull(d_sal_reb_sum,0) - isnull(d_com_sum,0),
d_np = isnull(d_sal_sum,0) - isnull(d_sal_pur_sum,0) - isnull(d_sal_reb_sum,0) - isnull(d_com_sum,0) - isnull(d_cost_sum,0),
d_npr=(case when isnull(d_sal_pur_sum,0)=0 then 0 else isnull(d_np,0)/d_sal_pur_sum end)
from 表名 a inner join inserted b on a.id=b.id
end
end
create trigger update_table on [表名]
for update
as
begin
if update(d_pur_sum) or update(d_sal_sum) or update(d_sal_pur_sum) or update(d_sal_reb_sum) or update(d_cost_sum) or update(d_com_sum)
begin
if(exists(select * from inserted where isnull(d_pur_sum,0)>0 and isnull(d_sal_sum,0)>0 and isnull(d_sal_pur_sum,0)>0 and isnull(d_sal_reb_sum,0)>0 and isnull(d_cost_sum,0)>0 and isnull(d_com_sum,0)>0))
begin
update a set
d_gp = isnull(d_sal_sum,0) - isnull(d_sal_pur_sum,0) - isnull(d_sal_reb_sum,0) - isnull(d_com_sum,0),
d_np = isnull(d_sal_sum,0) - isnull(d_sal_pur_sum,0) - isnull(d_sal_reb_sum,0) - isnull(d_com_sum,0) - isnull(d_cost_sum,0),
d_npr=(case when isnull(d_sal_pur_sum,0)=0 then 0 else isnull(d_np,0)/d_sal_pur_sum end)
from 表名 a inner join inserted b on a.id=b.id
end
end
end