create CreditCard_Cost(
ID INT,
Username varchar(20),
Bank varchar(20),
Monetray money,
Post_date datetime,
Ftype int
)
create --日志表
LOG_Card_Cost(
Username varchar(20),
Money_amount money,
Money1,
Money2,
)
create CreditCard_Type
Ftype int,
Properties varchar(20),
Mtype varchar(20)
测试数据:
insert into dbo.CreditCard_Cost
select '03011' ,'中国银行' ,'-200' ,GETDATE(),0
UNION ALL select'03012' ,'中国银行' ,'-300' ,GETDATE(),1
UNION ALL select '03013' ,'中国银行' ,'-400' ,GETDATE(),2
UNION ALL select '03014' ,'中国银行' ,'-500' ,GETDATE(),3
UNION ALL select '03015' ,'中国银行' ,'-600' ,GETDATE(),4
UNION ALL select '03016' ,'中国银行' ,'-700' ,GETDATE(),5insert into dbo.CreditCard_Type
select 0 ,-1,1
UNION ALL select 1,1,1
UNION ALL select 2,-1,2
UNION ALL select 3,1,2
UNION ALL select 4,-1,3
UNION ALL select 5,1,3
ALTER TRIGGER [dbo].[CreditCard_Cost_UPDATE] ON [dbo].[CreditCard_Cost]
AFTER UPDATE
AS
BEGIN
update t
set t.Money_amount = t.Money_amount + isnull(case when x.Mtype = 1 then x.Monetary1 end, 0)
- isnull(case when y.Mtype = 1 then y.Monetary2 end,0),
t.Money_1 = t.Money_1 + isnull(case when x.Mtype = 2 then x.Monetary1 end, 0)
- isnull(case when y.Mtype = 2 then y.Monetary2 end,0),
t.Money_2 = t.Money_2 + isnull(case when x.Mtype = 3 then x.Monetary1 end, 0)
- isnull(case when y.Mtype = 3 then y.Monetary2 end,0)
from LOG_Card_Cost t
LEFT join (select i.Username,sum( c.Properties*i.monetary ) Monetary1
,c.Mtype from inserted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as x on t.Username = x.Username
LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
,c.Mtype from deleted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as y on t.Username = y.Username
where x.Username is not null or y.Username is not null
END
我写的触发器如上,要求判断Mtype的值11,22,33不用case when 要怎么写,我想不出来,(老大要求)
CreditCard_Type,Ftype和Properties 字段不可以改变,其他可以扩展表
update t set ... where x.Mtype = 1 and y.Mtype = 1
update t set ... where x.Mtype = 2 and y.Mtype = 2
update t set ... where x.Mtype = 3 and y.Mtype = 3
不用case ,不用dawugui的这个方式,还真想不到别的合理点儿的了。
if @Mtype =1
elseif @Mtype =2
else @Mtype =3
AFTER UPDATE
AS
BEGIN
update t
set t.Money_amount = t.Money_amount + isnull(x.Monetary1, 0)
- isnull(y.Monetary2,0),
from LOG_Card_Cost t
LEFT join (select i.Username,sum( c.Properties*i.monetary ) Monetary1
,c.Mtype from inserted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as x on t.Username = x.Username
LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
,c.Mtype from deleted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as y on t.Username = y.Username
where x.Username is not null or y.Username is not null
and x.Mtype = 1 and y.Mtype = 1 update t
set t.Money_1 = t.Money_1 + isnull(x.Monetary1, 0)
- isnull(y.Monetary2,0),
from LOG_Card_Cost t
LEFT join (select i.Username,sum( c.Properties*i.monetary ) Monetary1
,c.Mtype from inserted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as x on t.Username = x.Username
LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
,c.Mtype from deleted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as y on t.Username = y.Username
where x.Username is not null or y.Username is not null
and x.Mtype = 2 and y.Mtype = 2
update t
set t.Money_2 = t.Money_2 + isnull(x.Monetary1, 0)
- isnull(y.Monetary2,0)
from LOG_Card_Cost t
LEFT join (select i.Username,sum( c.Properties*i.monetary ) Monetary1
,c.Mtype from inserted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as x on t.Username = x.Username
LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
,c.Mtype from deleted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as y on t.Username = y.Username
where x.Username is not null or y.Username is not null
and x.Mtype = 3 and y.Mtype = 3
END
------------------
是不觸發?還是
他的写法要类是 LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
,c.Mtype from deleted as i
LEFT JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype group by i.Username,c.Mtype
) as y on t.Username = y.Username
这样的想法,直接取出来....提示可以扩展CreditCard_Type表,(Ftype和Properties 字段不可以改变)
AFTER UPDATE
AS
BEGIN
UPDATE t
SET Money_amount=t.Money_amount
+ISNULL((select sum(c.Properties*i.monetary ) from inserted as i inner JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype AND c.Mtype =1 AND i.Username=t.Username),0)
-isnull((select sum(c.Properties*i.monetary ) from deleted as i INNER JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype AND c.Mtype =1 AND i.Username=t.Username),0)
,Money_1=Money_1
+ISNULL((select sum(c.Properties*i.monetary ) from inserted as i inner JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype AND c.Mtype =2 AND i.Username=t.Username),0)
-isnull((select sum(c.Properties*i.monetary ) from deleted as i INNER JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype AND c.Mtype =1 AND i.Username=t.Username),0)
,Money_2=Money_2
+ISNULL((select sum(c.Properties*i.monetary ) from inserted as i inner JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype AND c.Mtype =3 AND i.Username=t.Username),0)
-isnull((select sum(c.Properties*i.monetary ) from deleted as i INNER JOIN dbo.CreditCard_Type as c on i.Ftype=c.Ftype AND c.Mtype =3 AND i.Username=t.Username),0)
FROM LOG_Card_Cost T
END
如果要效率,分開效率高,先調用inserted,再用deleted減
改改
ALTER TRIGGER [dbo].[CreditCard_Cost_UPDATE] ON [dbo].[CreditCard_Cost]
AFTER UPDATE
AS
BEGIN
UPDATE a
SET Money_amount=a.Money_amount+b.Money_amount,
Money_1=a.Money_1+b.Money_1,
Money_2=a.Money_2+b.Money_2
FROM
LOG_Card_Cost AS a
INNER JOIN
(
SELECT
Username,Money_amount=SUM(CASE WHEN t3.Mtype =1 THEN monetary*Properties ELSE 0 END),Money_1=SUM(CASE WHEN t3.Mtype =2 THEN monetary*Properties ELSE 0 END),
Money_2=SUM(CASE WHEN t3.Mtype =3 THEN monetary*Properties ELSE 0 END)
FROM (SELECT Ftype,Username,SUM(monetary) AS monetary FROM (SELECT monetary,Ftype,Username FROM INSERTED UNION ALL SELECT -monetary,Ftype,Username FROM DELETED)t
GROUP BY Ftype,Username)t2
INNER JOIN CreditCard_Type AS t3 ON t2.Ftype=t3.Ftype
GROUP BY Username
)b on a.Username=b.Username
END
Username,Money_amount=SUM(CASE WHEN t3.Mtype =1 THEN monetary*Properties ELSE 0 END),Money_1=SUM(CASE WHEN t3.Mtype =2 THEN monetary*Properties ELSE 0 END),
Money_2=SUM(CASE WHEN t3.Mtype =3 THEN monetary*Properties ELSE 0 END)这不是也用到了case when,Mtype 的效果是为了影响Money_amount money,
Money1,
Money2,这3个的值,用1,1,2,2,3,3的来方法是我自己想出来的,我老大只是给了这个题目,但是我去交的时候,才叫我不要用case when
形成矩阵表
形成矩阵表