一个表中有字段riqi(表示日期）,aa（表示当日领的物资数量），totalaa(表示截止到当日领取的物资总量），我想写个触发器，当新增记录或修改或删除记录时修改所有记录的totalaa的值，请问怎么写？
------解决方案--------------------
写成存储过程吧
------解决方案--------------------
create table aa
(riqi datetime,
aname varchar(10) check (aname in ( 'A ', 'B ')),
aa int,
tatalaa int)
go
insert into aa
select '2007/5/25 ', 'A ', 10, 75 union
select '2007/5/25 ', 'B ', 2, 10 union
select '2007/5/24 ', 'A ', 15, 65 union
select '2007/5/23 ', 'A ', 20, 50 union
select '2007/5/22 ', 'A ', 30, 30 union
select '2007/5/22 ', 'B ', 1, 8
go
create trigger de_tatalaa
on aa
for delete
as
if @@rowcount <> 1
return
else
begin
declare @riqi1 datetime
declare @riqi2 datetime
declare @aa1 int
declare @aa2 int
declare @aname1 varchar(10)
declare @aname2 varchar(10)
--select @riqi1,@aa1=aa,@aname1=aname from inserted
select @riqi2=riqi,@aa2=aa,@aname2=aname from deleted
--select @dif=@aa1-@aa2
update aa set [email protected] where [email protected] and riqi> [email protected]
end
go
create trigger up_tatalaa
on aa
for update
as
if @@rowcount <> 1
return
else
begin
declare @riqi1 datetime
declare @riqi2 datetime
declare @aa1 int
declare @aa2 int
declare @aname1 varchar(10)
declare @aname2 varchar(10)
declare @dif int
select @riqi1=riqi,@aa1=aa,@aname1=aname from inserted
select @riqi2=riqi,@aa2=aa,@aname2=aname from deleted
select @dif=@aa1-@aa2
update aa set [email protected] where [email protected] and riqi> [email protected]
end
go
update aa set aa=10 where aname= 'A ' and riqi= '2007/5/24 '
select * from aa
没想明白你这么写有什么用。。。
------解决方案--------------------
这样的需求,分开表来处理好点
------解决方案--------------------
打击俺积极性啊，大哥，一看就知道是你表的设计逻辑问题，
“这样的需求,分开表来处理好点“
Yang_(扬帆破浪)，呵呵，用变量貌似容易看些，弄巧成拙呀。如果非要insert 以前日期的话，trigger里加个判断就好：if exsits (select * from aa where riqi> (select riqi from inserted)),另外貌似不可能有一次修改多条数据的可能性吧。。
------解决方案--------------------
如果数据量不大，不怕更新整个表，可以这样简化触发器（用非触发器的思想写触发器，罪过）
create trigger tr_aa
on aa
for insert,update,delete
as
update a
set tatalaa=(select sum(aa) from aa where aname=a.aname
and riqi <=a.riqi)
from aa a,(
select aname,min(riqi) as riqi from (
select aname,riqi from inserted
union all
select aname,riqi from deleted
) as t1
group by aname
) as t
where a.aname=t.aname
and a.riqi> =i.riqi
go