最近公司的客户数据出现异常,数据库的数据没有任何关系和数据约束,搞到数据乱七八糟,真晕,拿来运营的的数据,设计成这样~~~~~~???没办法,现在只能见虫捉虫了,不过也好让我又来第一次,哈哈。学会感激~!!
/*=================================
OBJECT NAEM: trg_syncOutDot
DESCRIPTION: 同步消费点值
INPUT
OUTPU:
RELATED TABLE: gift_incept, charge_mst
AUTHOR: Kemin
DATE: 2005-5-26
MODIFIED:
===================================*/
CREATE TRIGGER trg_syncOutDot ON gift_incept
FOR DELETE
AS
DECLARE @Dot_Out_All AS bigint --赠送礼品总点值
DECLARE @Sender AS varchar(15) --赠送会员
DECLARE @Dot_Gift_Now AS varchar(15) --现存礼品总点值
--从删除表取得消费会员号
SELECT @Sender = sender FROM DELETED
--从消费历史算得消费总额(删除后)
SELECT @Dot_Out_All = SUM(charge)
FROM gift_incept t
INNER JOIN gif_gift g
ON t.lpid = g.id
WHERE sender = @Sender
--现存礼品总点值
SELECT @Dot_Gift_Now = SUM(num*charge)
FROM gif_list l
INNER JOIN gif_gift g
ON l.lpid = g.id
WHERE hyid = @Sender
--更新汇总数据:现在会员消费总点值 = 现存礼品总点值 + 从消费历史算得消费总额(删除后)
UPDATE charge_mst
SET expenditure = @Dot_Out_All + @Dot_Gift_Now
WHERE userid = @Sender
GO