有2个表
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,
)
insert into dbo.CreditCard_Cost
select '1' ,'张三' ,'中国银行' ,'300' ,GETDATE(),'0'
UNION ALL select '2' ,'仔仔' ,'中国银行' ,'400' ,GETDATE(),'0'
UNION ALL select '3' ,'马六' ,'中国银行' ,'500' ,GETDATE(),'0'
UNION ALL select '4' ,'万万' ,'中国银行' ,'600' ,GETDATE(),'0'
UNION all select '5' ,'楚楚' ,'中国银行' ,'300' ,GETDATE(),'0'
UNION ALL select '6' ,'万万' ,'中国银行' ,'400' ,GETDATE(),'0'
UNION ALL select '7' ,'李四' ,'中国银行' ,'500' ,GETDATE(),'0'
UNION ALL select '8' ,'万万' ,'中国银行' ,'-600' ,GETDATE(),'1'
UNION all select '9' ,'王文' ,'中国银行' ,'-300' ,GETDATE(),'1'
UNION ALL select '10' ,'师大' ,'中国银行' ,'-400' ,GETDATE(),'1'
UNION ALL select '11' ,'万万' ,'中国银行' ,'-500' ,GETDATE(),'1'
UNION ALL select '12' ,'张三' ,'中国银行' ,'-600' ,GETDATE(),'1'写一个触发器,要求插入数据后,日志表显示用户的消费总和
要求结果类似如下
Username Money_amount
----------
大苏 600.00
吉萨 700.00
李四 400.00
马六 500.00
奈斯 800.00
万万 -2600.00
张三 900.00
我写的触发器:但是有问题,找不出来
ALTER TRIGGER [dbo].[tr_Card_cost]
ON [dbo].[CreditCard_Cost]
AFTER INSERT,DELETE,UPDATE
AS
SET NOCOUNT ON;
DECLARE @insert MONEY,@delete MONEY,@a int ,@b int
IF not exists(select top 1* from inserted a left join LOG_Card_Cost b on a.username=B.username where B.username is null)
or not exists(select top 1* from deleted a left join LOG_Card_Cost b on a.username=B.username where B.username is null)
begin
INSERT dbo.LOG_Card_Cost(Username,Money_amount)
select A.Username,sum(case when (A.monetary>0 and A.ftype=1) then -1*A.monetary
when (A.monetary<0 and A.ftype=0) then -1*A.monetary
else A.monetary end) as monetary
from dbo.CreditCard_Cost A
left join inserted B on a.Username=b.Username
left join deleted C on a.Username=c.Username
where b.Username is not null or c.Username is not null
group by A.Username
end
else
BEGIN
select @a=count(*) from inserted
select @b=count(*) from deleted
IF @a>0 AND @b=0
BEGIN--增加
update dbo.LOG_Card_Cost set Money_amount=a.Money_amount+b.monetary from LOG_Card_Cost A
left join (select Username,sum(monetary) monetary from inserted group by Username) B on a.Username=b.Username
END
IF @a>0 AND @b>0
BEGIN--修改
update dbo.LOG_Card_Cost set Money_amount=a.Money_amount+b.monetary-c.monetary from LOG_Card_Cost A
left join (select Username,sum(monetary) monetary from inserted group by username) B on a.username=b.username
left join (select Username,sum(monetary) monetary from deleted group by username) C on a.username=c.username
END
IF @a=0 AND @b>0
BEGIN--删除
update dbo.LOG_Card_Cost set Money_amount=a.Money_amount-b.monetary from LOG_Card_Cost A
left join (select Username,sum(monetary) monetary from deleted group by username) B on a.username=b.username
END
END
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,
)
insert into dbo.CreditCard_Cost
select '1' ,'张三' ,'中国银行' ,'300' ,GETDATE(),'0'
UNION ALL select '2' ,'仔仔' ,'中国银行' ,'400' ,GETDATE(),'0'
UNION ALL select '3' ,'马六' ,'中国银行' ,'500' ,GETDATE(),'0'
UNION ALL select '4' ,'万万' ,'中国银行' ,'600' ,GETDATE(),'0'
UNION all select '5' ,'楚楚' ,'中国银行' ,'300' ,GETDATE(),'0'
UNION ALL select '6' ,'万万' ,'中国银行' ,'400' ,GETDATE(),'0'
UNION ALL select '7' ,'李四' ,'中国银行' ,'500' ,GETDATE(),'0'
UNION ALL select '8' ,'万万' ,'中国银行' ,'-600' ,GETDATE(),'1'
UNION all select '9' ,'王文' ,'中国银行' ,'-300' ,GETDATE(),'1'
UNION ALL select '10' ,'师大' ,'中国银行' ,'-400' ,GETDATE(),'1'
UNION ALL select '11' ,'万万' ,'中国银行' ,'-500' ,GETDATE(),'1'
UNION ALL select '12' ,'张三' ,'中国银行' ,'-600' ,GETDATE(),'1'写一个触发器,要求插入数据后,日志表显示用户的消费总和
要求结果类似如下
Username Money_amount
----------
大苏 600.00
吉萨 700.00
李四 400.00
马六 500.00
奈斯 800.00
万万 -2600.00
张三 900.00
我写的触发器:但是有问题,找不出来
ALTER TRIGGER [dbo].[tr_Card_cost]
ON [dbo].[CreditCard_Cost]
AFTER INSERT,DELETE,UPDATE
AS
SET NOCOUNT ON;
DECLARE @insert MONEY,@delete MONEY,@a int ,@b int
IF not exists(select top 1* from inserted a left join LOG_Card_Cost b on a.username=B.username where B.username is null)
or not exists(select top 1* from deleted a left join LOG_Card_Cost b on a.username=B.username where B.username is null)
begin
INSERT dbo.LOG_Card_Cost(Username,Money_amount)
select A.Username,sum(case when (A.monetary>0 and A.ftype=1) then -1*A.monetary
when (A.monetary<0 and A.ftype=0) then -1*A.monetary
else A.monetary end) as monetary
from dbo.CreditCard_Cost A
left join inserted B on a.Username=b.Username
left join deleted C on a.Username=c.Username
where b.Username is not null or c.Username is not null
group by A.Username
end
else
BEGIN
select @a=count(*) from inserted
select @b=count(*) from deleted
IF @a>0 AND @b=0
BEGIN--增加
update dbo.LOG_Card_Cost set Money_amount=a.Money_amount+b.monetary from LOG_Card_Cost A
left join (select Username,sum(monetary) monetary from inserted group by Username) B on a.Username=b.Username
END
IF @a>0 AND @b>0
BEGIN--修改
update dbo.LOG_Card_Cost set Money_amount=a.Money_amount+b.monetary-c.monetary from LOG_Card_Cost A
left join (select Username,sum(monetary) monetary from inserted group by username) B on a.username=b.username
left join (select Username,sum(monetary) monetary from deleted group by username) C on a.username=c.username
END
IF @a=0 AND @b>0
BEGIN--删除
update dbo.LOG_Card_Cost set Money_amount=a.Money_amount-b.monetary from LOG_Card_Cost A
left join (select Username,sum(monetary) monetary from deleted group by username) B on a.username=b.username
END
END
2句update和insert就行了
FOR INSERT,UPDATE,DELETE
AS
BEGIN
UPDATE b
SET Money_amount=b.Money_amount-a.Monetray
FROM (SELECT Username,SUM(Monetray) AS Monetray FROM DELETED GROUP BY Username)a
INNER JOIN LOG_Card_Cost AS b ON a.Username=b.Username
UPDATE b
SET Money_amount=b.Money_amount+a.Monetray
FROM (SELECT Username,SUM(Monetray) AS Monetray FROM INSERTED GROUP BY Username)a
INNER JOIN LOG_Card_Cost AS b ON a.Username=b.Username INSERT LOG_Card_Cost(Username,Money_amount)
SELECT
a.Username,a.Monetray
FROM (SELECT Username,SUM(Monetray) AS Monetray FROM INSERTED GROUP BY Username)a
left JOIN LOG_Card_Cost AS b ON a.Username=b.Username
WHERE b.Username IS null
END
create trigger [dbo].[tr_Card_cost]
ON [dbo].[CreditCard_Cost]
after insert,update,delete
as
begin
if not exists (select 1 from inserted) --删除记录
update t
set t.Money_amount = t.Money_amount - e.Monetray
from LOG_Card_Cost t join (select Username,sum(Monetray) Monetray from deleted group by Username) e
on t.Username = e.Username
else --新增和修改记录
begin
insert into LOG_Card_Cost
select Username,sum(Monetray)
from inserted t
where not exists (select 1 from LOG_Card_Cost where Username = t.Username)update t
set t.Money_amount = t.Money_amount - isnull(f.Monetray,0) + isnull(e.Monetray,0)
from LOG_Card_Cost t left join (select Username,sum(Monetray) Monetray from inserted group by Username) e
on t.Username = e.Username
left join (select Username,sum(Monetray) Monetray from deleted group by Username) f
on t.Username = f.Username
end
end
go
这样试试!
FOR INSERT,UPDATE,DELETE
AS
BEGIN
UPDATE b
SET Money_amount=b.Money_amount-ISNULL(a.Monetray,0)+ISNULL(c.Monetray,0)
FROM LOG_Card_Cost AS b
LEFT JOIN (SELECT Username,SUM(Monetray) AS Monetray FROM DELETED GROUP BY Username)a ON a.Username=b.Username
LEFT JOIN (SELECT Username,SUM(Monetray) AS Monetray FROM INSERTED GROUP BY Username)c ON c.Username=b.Username
INSERT LOG_Card_Cost(Username,Money_amount)
SELECT
a.Username,a.Monetray
FROM (SELECT Username,SUM(Monetray) AS Monetray FROM INSERTED GROUP BY Username)a
left JOIN LOG_Card_Cost AS b ON a.Username=b.Username
WHERE b.Username IS null
END
FOR INSERT,UPDATE,DELETE
AS
BEGIN
UPDATE b
SET Money_amount=b.Money_amount-ISNULL(a.Monetray,0)+ISNULL(c.Monetray,0)
FROM LOG_Card_Cost AS b
LEFT JOIN (SELECT Username,SUM(Monetray) AS Monetray FROM DELETED GROUP BY Username)a ON a.Username=b.Username
LEFT JOIN (SELECT Username,SUM(Monetray) AS Monetray FROM INSERTED GROUP BY Username)c ON c.Username=b.Username
INSERT LOG_Card_Cost(Username,Money_amount)
SELECT
a.Username,a.Monetray
FROM (SELECT Username,SUM(Monetray) AS Monetray FROM INSERTED GROUP BY Username)a
left JOIN LOG_Card_Cost AS b ON a.Username=b.Username
WHERE b.Username IS NULL
DELETE a FROM LOG_Card_Cost AS a WHERE EXISTS(SELECT 1 FROM DELETED WHERE Username=a.Username) AND a.Money_amount=0
END
ON [dbo].[CreditCard_Cost]
after insert,update,delete
as
begin
if not exists (select 1 from inserted) --删除记录
update t
set t.Money_amount = t.Money_amount - e.Monetray
from LOG_Card_Cost t join (select Username,sum(Monetray) Monetray from deleted group by Username) e
on t.Username = e.Username
else --新增和修改记录
begin
insert into LOG_Card_Cost
select Username,sum(Monetray)
from inserted t
where not exists (select 1 from LOG_Card_Cost where Username = t.Username)update t
set t.Money_amount = t.Money_amount - isnull(f.Monetray,0) + isnull(e.Monetray,0)
from LOG_Card_Cost t left join (select Username,sum(Monetray) Monetray from inserted group by Username) e
on t.Username = e.Username
left join (select Username,sum(Monetray) Monetray from deleted group by Username) f
on t.Username = f.Usernamedelete a from LOG_Card_Cost a where exists(SELECT 1 FROM DELETED WHERE Username=a.Username) AND a.Money_amount=0
ENDend
end
go
我的思路是
第一次:计算出CreditCard_Cost第一次的值,插入LOG表中
根据count(*)判断
1增加 update set log表的中的字段(取出log中的值减去insert)
2删除 update set
3修改 update set