请帮写一个触发器

[zhegenew126bano]

字段名：

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 的一个百分比，保留两位小数。

[ycj80]

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

[Neo_whl]

不知能否将 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 table tb
(
...,
  d_gp as (d_sal_sum - d_sal_pur_sum - d_sal_reb_sum - d_com_sum),
  d_np as (d_sal_sum - d_sal_pur_sum - d_sal_reb_sum - d_com_sum - d_cost_sum),
  d_npr as d_np/d_sal_pur_sum
)    

[上海_童鞋]

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

[zhegenew126bano]

引用 1 楼 changjiu_yang 的回复:

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

提示在 “begin”处有语法错误，触发器完全不懂，麻烦把代码补全，谢谢。

[zhegenew126bano]

引用 3 楼 gj_diy 的回复:

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

提示所有的"列名"都是不明确

[上海_童鞋]

1.你把那个表名换成你的实际数据表呀。 2. 把id改为d_id

[zhegenew126bano]

引用 6 楼 gj_diy 的回复:

1.你把那个表名换成你的实际数据表呀。 2. 把id改为d_id

是把表名和，ID名都换了后出现那个提示