if update(a.num)
update b
set allnowsum=sum(a.allnow)
FROM A
WHERE A。PK = B。PK
update b
set allnowsum=sum(a.allnow)
FROM A
WHERE A。PK = B。PK
解决方案 »
- 菜鸟问题,GROUP BY
- 散分前十 看到好多没分数的贴子
- 关于存储过程加密的一些问题
- 求一条简单的SQL语句
- 如何还原已经存在的同名的数据库,请大家指教,在此表示感谢了!!!!
- 对两个数据库操作如何实现回滚?
- smalldatetim类型select 出来格式问题
- 存储过程???
- 日期问题:怎么取‘2002-6-21 12:00:00’的'2002-6-21',不要后面的时间'12:00:00'
- 重装SQL SERVER之后SQL MAIL不能使用
- VC和SQLserver2000可以连么???如何连???????????????
- 请教,SQL 中的聚集索引和非聚集索引 分别指的是什么? 请帮帮忙!
create trigger tr_Test
on TableName
for insert,update,delete
asselect pk,sum(ALLNOW) as ALLNOW into #Temp from insertedinsert #Temp select pk,sum(-ALLNOW) as ALLNOW from deletedselect pk,sum(ALLNOW) as ALLNOW into #Sum from #Tempupdate b
set b.ALLNOW=b.ALLNOW+t.ALLNOW
from b,#Sum t
where b.Pk=t.Pkinsert b(Pk,ALLNOW)
select pk,ALLNOW
from #Sum
where not exists (select * from B where #Sum.Pk=b.Pk)go这是个事例的写法,你要根据你的应用修改!
A表的结构:PK,ALLIN , ALLOUT ,日期
b表的结构:PK,ALLIN , ALLOUT , ALLNOW...
其中B表的ALLNOW应该等于allin - allout,
而B表的ALLIN,ALLOUT等于SUM(ALLIN),SUM(ALLOUT) FROM A WHEHRE A.PK = B.PK
那么这个触发器怎么写呢?马上结帐,谢谢!
on A
for insert,update,delete
asselect pk,sum(ALLIN) as ALLIN,sum(ALLOUT) as ALLOUT into #Temp from insertedinsert #Temp select pk,sum(-ALLIN) as ALLIN,sum(-ALLOUT) as ALLOUT from deletedselect pk,sum(ALLIN) as ALLIN,sum(ALLOUT) as ALLOUT into #Sum from #Tempupdate b
set ALLIN=b.ALLIN+t.ALLIN,
ALLOUT=b.ALLOUT+t.ALLOUT,
ALLNOW=b.ALLNOW+t.ALLIN-t.ALLOUT
from b,#Sum t
where b.Pk=t.Pkinsert b(Pk,ALLIN,ALLOUT,ALLNOW)
select pk,ALLIN,ALLOUT,ALLIN-ALLOUT AS ALLNOW
from #Sum
where not exists (select * from B where #Sum.Pk=b.Pk)go
能交个朋友吗,我是一个PB的程序员(只精通这个东西,不要见笑),希望多学习SQL语言,可以告诉我您的QQ或信箱吗?
马上就结帐了,希望能回一下