一楼的有问题,一次插入多条同一个考生身份证号的记录有问题CREATE TRIGGER 名1 ON 考试成绩表 FOR INSERT,Update,delete AS set nocount onselect 考生身份证号,sum(分数) as 分数 into #tmp from ( select 考生身份证号,分数 from inserted union all select 考生身份证号,-分数 from deleted ) as xupdate 报名表 set 总分=报名表.总分+tem.分数 from $tmp tem where 报名表.考生身份证号=tem.考生身份证号drop table #tmpgo
to 楼上你没试过怎么知道有问题?CREATE TRIGGER 名1 ON 考试成绩表 FOR delete AS update 报名表 set 总分=报名表.总分-tem.分数 from deleted tem where 报名表.考生身份证号=tem.考生身份证号 CREATE TRIGGER 名1 ON 考试成绩表 FOR update AS update 报名表 set 总分=报名表.总分+tem.分数 from inserted tem where 报名表.考生身份证号=tem.考生身份证号 update 报名表 set 总分=报名表.总分-tem.分数 from deleted tem where 报名表.考生身份证号=tem.考生身份证号
先改正自己的错误:CREATE TRIGGER 名1 ON 考试成绩表 FOR INSERT,Update,delete AS set nocount onselect 考生身份证号,sum(分数) as 分数 into #tmp from ( select 考生身份证号,分数 from inserted union all select 考生身份证号,-分数 from deleted ) as x group by 考生身份证号 update 报名表 set 总分=报名表.总分+tem.分数 from $tmp tem where 报名表.考生身份证号=tem.考生身份证号drop table #tmpgo 再回答MVP的问题: 我试过,或者说是我以前犯过的错误,你不信试一下这样的插入语句:insert 考试成绩表(id,科目编号,考生身份证号,分数) select 1,'语文','00001',80 union all select 2,'数学','00001',90
自己的问题没改正完,再改正:CREATE TRIGGER 名1 ON 考试成绩表 FOR INSERT,Update,delete AS set nocount onselect 考生身份证号,sum(分数) as 分数 into #tmp from ( select 考生身份证号,分数 from inserted union all select 考生身份证号,-分数 from deleted ) as x group by 考生身份证号 update 报名表 set 总分=报名表.总分+tem.分数 from #tmp tem where 报名表.考生身份证号=tem.考生身份证号drop table #tmpgo
to 楼上:update 报名表 set 总分=报名表.总分+tem.分数 from inserted tem --------------------------------------------^^^^^^^^^^^^^^^^^^^^这里与主表关联 where 报名表.考生身份证号=tem.考生身份证号 -------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^这里
FOR INSERT,Update,delete
AS
set nocount onselect 考生身份证号,sum(分数) as 分数
into #tmp
from (
select 考生身份证号,分数 from inserted
union all
select 考生身份证号,-分数 from deleted
) as xupdate 报名表 set 总分=报名表.总分+tem.分数
from $tmp tem
where 报名表.考生身份证号=tem.考生身份证号drop table #tmpgo
FOR delete
AS
update 报名表 set 总分=报名表.总分-tem.分数 from deleted tem where 报名表.考生身份证号=tem.考生身份证号
CREATE TRIGGER 名1 ON 考试成绩表
FOR update
AS
update 报名表 set 总分=报名表.总分+tem.分数 from inserted tem where 报名表.考生身份证号=tem.考生身份证号
update 报名表 set 总分=报名表.总分-tem.分数 from deleted tem where 报名表.考生身份证号=tem.考生身份证号
FOR INSERT,Update,delete
AS
set nocount onselect 考生身份证号,sum(分数) as 分数
into #tmp
from (
select 考生身份证号,分数 from inserted
union all
select 考生身份证号,-分数 from deleted
) as x
group by 考生身份证号
update 报名表 set 总分=报名表.总分+tem.分数
from $tmp tem
where 报名表.考生身份证号=tem.考生身份证号drop table #tmpgo
再回答MVP的问题:
我试过,或者说是我以前犯过的错误,你不信试一下这样的插入语句:insert 考试成绩表(id,科目编号,考生身份证号,分数)
select 1,'语文','00001',80
union all
select 2,'数学','00001',90
FOR INSERT,Update,delete
AS
set nocount onselect 考生身份证号,sum(分数) as 分数
into #tmp
from (
select 考生身份证号,分数 from inserted
union all
select 考生身份证号,-分数 from deleted
) as x
group by 考生身份证号
update 报名表 set 总分=报名表.总分+tem.分数
from #tmp tem
where 报名表.考生身份证号=tem.考生身份证号drop table #tmpgo
--------------------------------------------^^^^^^^^^^^^^^^^^^^^这里与主表关联
where 报名表.考生身份证号=tem.考生身份证号
-------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^这里