当前位置: 代码迷 >> Sql Server >> 这个触发器如何写
  详细解决方案

这个触发器如何写

热度:100   发布时间:2016-04-27 17:42:28.0
这个触发器怎么写?
一个表中有字段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
  相关解决方案