34,576
社区成员
发帖
与我相关
我的任务
分享
--表 tb_Customer
cusId balance
---------------------
/*
cusId:客户的ID
balance:客户的余额
*/
--表 tb_Records
cusId amount currentbalance createddate
--------------------------------------------
/*
cusId: 客户的ID号(与表tb_Customer.cusId 相对应)
amount:汇入或支付的金额(若为正表示客户的余额增多,负则表示减少)
currentbalance:汇入或支出后的余额 (当前行的currentbalance总是为上一行的currentbalance+当前后之后的值)
createddate:该条记录创建的时间(值总是getdate())
*/
/*需求:---------------
1:
若向表tb_Records插入一条数据如:insert into tb_Records (cusId,amount) values (12,220.00)
执行这一条语句之后,我想(用触发器)自动update当前行的currentbalance,
currentbalance等所插入的amount 加上这个客户上次的currentbalance或表tb_Customer的balance
(因为tb_Customer.balance一直等于tb_Records.currentbalance)
2:
为了使tb_Customer.balance一直等于tb_Records.currentbalance,在更新了tb_Records.currentbalance
之后,再同时要更新这个客户的tb_Customer.balance
*/
create table tb_Customer(cusId int , balance decimal(18,2))
create table tb_Records(cusId int,amount decimal(18,2),currentbalance decimal(18,2) ,createddate datetime)
go
create trigger mytrig on tb_Records after insert
as
if not exists(select 1 from tb_Customer where cusId = (select cusId from inserted))
begin
insert into tb_Customer select cusId ,amount from inserted
update tb_Records set currentbalance = i.amount from tb_Records , inserted i where tb_Records.cusid = i.cusid
end
else
begin
update tb_Records set currentbalance = isnull((select top 1 currentbalance from (select top 2 currentbalance , createddate from tb_Records where cusid = m.cusid order by createddate desc) t order by t.createddate) + i.amount,m.currentbalance) from tb_Records m, inserted i where m.cusid = i.cusid and m.createddate = (select max(createddate) from tb_Records where cusid = m.cusid)
update tb_Customer set balance = balance + i.amount from tb_Customer , inserted i where tb_Customer.cusId = i.cusId
end
go
insert into tb_records(cusId,amount,createddate) values (12,220.00 , '2009-01-01')
select * from tb_Customer
select * from tb_Records
/*
cusId balance
----------- --------------------
12 220.00
(所影响的行数为 1 行)
cusId amount currentbalance createddate
----------- -------------------- -------------------- ------------------------------------------------------
12 220.00 220.00 2009-01-01 00:00:00.000
(所影响的行数为 1 行)
*/
insert into tb_records(cusId,amount,createddate) values (12,210.00 , '2009-01-02')
select * from tb_Customer
select * from tb_Records
/*
cusId balance
----------- --------------------
12 430.00
(所影响的行数为 1 行)
cusId amount currentbalance createddate
----------- -------------------- -------------------- ------------------------------------------------------
12 220.00 220.00 2009-01-01 00:00:00.000
12 210.00 430.00 2009-01-02 00:00:00.000
(所影响的行数为 2 行)
*/
insert into tb_records(cusId,amount,createddate) values (12,200.00 , '2009-01-03')
select * from tb_Customer
select * from tb_Records
/*
cusId balance
----------- --------------------
12 630.00
(所影响的行数为 1 行)
cusId amount currentbalance createddate
----------- -------------------- -------------------- ------------------------------------------------------
12 220.00 220.00 2009-01-01 00:00:00.000
12 210.00 430.00 2009-01-02 00:00:00.000
12 200.00 630.00 2009-01-03 00:00:00.000
(所影响的行数为 3 行)
*/
drop table tb_Customer,tb_Records
Use tempdb
Go
create table tb_Customer
(
cusId int not null,
balance smallmoney not null
)
GO
create table tb_Records
(
cusId int not null,
amount smallmoney not null,
currentbalance smallmoney,
createddate datetime
)
GO
create trigger tr_records on tb_Records
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO tb_Records
SELECT i.cusId,i.amount,c.balance+i.amount,getdate()
FROM tb_Customer c
inner join inserted i on c.cusId=i.cusId;
Update tb_Customer
set balance=balance+i.amount
FROM tb_Customer c
inner join inserted i on c.cusId=i.cusId;
END
GO
insert into tb_Customer values (12,100.00);
insert into tb_Records (cusId,amount) values (12,120.00);
select * from tb_Customer;
select * from tb_Records;
cusId balance
----------- ---------------------
12 220.00
(1 行受影响)
cusId amount currentbalance createddate
----------- --------------------- --------------------- -----------------------
12 120.00 220.00 2009-01-08 22:02:57.640
(1 行受影响)