create trigger pur_U on 表 for update
asif 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
asif 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
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_sumd_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
endcreate 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
提示在 “begin”处有语法错误,触发器完全不懂,麻烦把代码补全,谢谢。
2. 把id改为d_id