sqlserver触发器

——插入
ALTER TRIGGER [dbo].[TRIGGER_Insert_WxPartnerBonusDetail] ON [dbo].[WxPartnerBonusDetail]
FOR insert
AS

DECLARE @Rid Int
DECLARE @PartnerCustNo VARCHAR(50)
DECLARE @BonusMoney NUMERIC(24,10)
DECLARE @TakeMoney NUMERIC(24,10)
DECLARE @Balance NUMERIC(24,10)
BEGIN
Declare CurTest Cursor local Scroll for
SELECT rid,PartnerCustNo,ISNULL(BonusMoney,0),ISNULL(TakeMoney,0)
FROM Inserted
open CurTest
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney

while (@@fetch_status = 0)
BEGIN
select @Balance = ISNULL(Balance,0.0)
from WxPartnerBonusDetail
where Rid = (select MAX(rid) from WxPartnerBonusDetail where PartnerCustNo = @PartnerCustNo and Rid < @rid)
set @Balance = ISNULL(@Balance,0.0)
update m set Balance = @Balance + isnull((select sum(ISNULL(BonusMoney,0) - ISNULL(TakeMoney,0))
from WxPartnerBonusDetail mm
WHERE PartnerCustNo = @PartnerCustNo and mm.rid >= @rid and mm.Rid <= m.rid),0.0)
from WxPartnerBonusDetail m
where PartnerCustNo = @PartnerCustNo and m.rid >= @rid


select @Balance = ISNULL(Balance,0.0)
from WxPartnerBonusDetail
where Rid = (select MAX(rid) from WxPartnerBonusDetail where PartnerCustNo = @PartnerCustNo)

update WxPartnerInfo set BonusBalance = @Balance
where custno = @PartnerCustNo
PRINT ‘TRIGGER_Insert_WxPartnerBonusDetail’
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney



end

CLOSE CurTest
DEALLOCATE CurTest

END
—删除
ALTER TRIGGER [dbo].[TRIGGER_Delete_WxPartnerBonusDetail] ON [dbo].[WxPartnerBonusDetail]
FOR Delete
AS

DECLARE @Rid Int
DECLARE @PartnerCustNo VARCHAR(50)
DECLARE @BonusMoney NUMERIC(24,10)
DECLARE @TakeMoney NUMERIC(24,10)
DECLARE @Balance NUMERIC(24,10)
BEGIN
Declare CurTest Cursor local Scroll for
SELECT rid,PartnerCustNo,ISNULL(BonusMoney,0),ISNULL(TakeMoney,0)
FROM Deleted
open CurTest
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney
while (@@fetch_status = 0)
begin
select @Balance = ISNULL(Balance,0.0)
from WxPartnerBonusDetail
where Rid = (select MAX(rid) from WxPartnerBonusDetail where PartnerCustNo = @PartnerCustNo and Rid < @rid)
set @Balance = ISNULL(@Balance,0.0)
update m set Balance = @Balance + isnull((select sum(ISNULL(BonusMoney,0) - ISNULL(TakeMoney,0))
from WxPartnerBonusDetail mm
WHERE PartnerCustNo = @PartnerCustNo and mm.rid >= @rid and mm.Rid <= m.rid),0.0)
from WxPartnerBonusDetail m
where PartnerCustNo = @PartnerCustNo and m.rid >= @rid

select @Balance = ISNULL(Balance,0.0)
from WxPartnerBonusDetail
where Rid = (select MAX(rid) from WxPartnerBonusDetail where PartnerCustNo = @PartnerCustNo)

update WxPartnerInfo set BonusBalance = @Balance
where custno = @PartnerCustNo
PRINT ‘TRIGGER_Delete_WxPartnerBonusDetail’
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney
end

CLOSE CurTest
DEALLOCATE CurTest

END
—更新
ALTER TRIGGER [dbo].[TRIGGER_Update_WxPartnerBonusDetail] ON [dbo].[WxPartnerBonusDetail]
FOR Update
AS

DECLARE @Rid Int
DECLARE @PartnerCustNo VARCHAR(50)
DECLARE @BonusMoney NUMERIC(24,10)
DECLARE @TakeMoney NUMERIC(24,10)
DECLARE @Balance NUMERIC(24,10)
begin

Declare CurTest Cursor local Scroll for
SELECT rid,PartnerCustNo,ISNULL(BonusMoney,0),ISNULL(TakeMoney,0)
FROM inserted
open CurTest
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney
while (@@fetch_status = 0)
begin
if exists(select 1 from deleted a left join inserted b on a.rid = b.rid where a.BonusMoney=b.BonusMoney and a.takemoney = b.takemoney and a.rid= @rid)
begin
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney
continue
end
select @Balance = ISNULL(Balance,0.0)
from WxPartnerBonusDetail
where Rid = (select MAX(rid) from WxPartnerBonusDetail where PartnerCustNo = @PartnerCustNo and Rid < @rid)
set @Balance = ISNULL(@Balance,0.0)
update m set Balance = @Balance + isnull((select sum(ISNULL(BonusMoney,0) - ISNULL(TakeMoney,0))
from WxPartnerBonusDetail mm
WHERE PartnerCustNo = @PartnerCustNo and mm.rid >= @rid and mm.Rid <= m.rid),0.0)
from WxPartnerBonusDetail m
where PartnerCustNo = @PartnerCustNo and m.rid >= @rid
select @Balance = ISNULL(Balance,0.0)
from WxPartnerBonusDetail
where Rid = (select MAX(rid) from WxPartnerBonusDetail where PartnerCustNo = @PartnerCustNo)
update WxPartnerInfo set BonusBalance = @Balance
where custno = @PartnerCustNo

PRINT ‘TRIGGER_Update_WxPartnerBonusDetail’
fetch next from CurTest into @rid,@PartnerCustNo,@BonusMoney,@TakeMoney
end
CLOSE CurTest
DEALLOCATE CurTest

END