创建Trade表
create table Trade
(
TradeID bigint identity primary key,
CardID char(10) foreign key(CardID) references BankCard(CardID),
TradeDate datetime not null,
TradeMoney money check(TradeMoney>0),
TradeType char(2)
)
CREATE TRIGGER trig_transInfo
ON Trade
FOR INSERT
AS
begin
DECLARE @type char(4);
declare @outMoney MONEY;
DECLARE @myCardID char(10);
declare @balance MONEY;
SELECT @type=TradeType,@outMoney=TradeMoney,
@myCardID=CardID FROM inserted
IF (@type='取钱')
UPDATE BankCard SET Payout=Payout+@outMoney WHERE CardID=@myCardID
ELSE
UPDATE BankCard SET Deposit=Deposit+@outMoney WHERE CardID=@myCardID
end
go
drop trigger trig_transInfo
---------------------
insert into BankCard values
('1000000001','招行','关羽',0,0
)
insert into BankCard values
('2000000001','工行','张飞',0,0
)
insert into Trade values('2000000001',GETDATE(),1000,'存钱')
insert into Trade values('1000000001',GETDATE(),1000,'取钱')
create table Trade
(
TradeID bigint identity primary key,
CardID char(10) foreign key(CardID) references BankCard(CardID),
TradeDate datetime not null,
TradeMoney money check(TradeMoney>0),
TradeType char(2)
)
创建BankCard表
create table BankCard
(
CardID char(10) primary key,
BankName varchar(20) not null,
UserName varchar(20) not null,
Deposit money not null,
Payout money not null
)
--------------在Trade表上创建触发器CREATE TRIGGER trig_transInfo
ON Trade
FOR INSERT
AS
begin
DECLARE @type char(4);
declare @outMoney MONEY;
DECLARE @myCardID char(10);
declare @balance MONEY;
SELECT @type=TradeType,@outMoney=TradeMoney,
@myCardID=CardID FROM inserted
IF (@type='取钱')
UPDATE BankCard SET Payout=Payout+@outMoney WHERE CardID=@myCardID
ELSE
UPDATE BankCard SET Deposit=Deposit+@outMoney WHERE CardID=@myCardID
end
go
drop trigger trig_transInfo
---------------------
insert into BankCard values
('1000000001','招行','关羽',0,0
)
insert into BankCard values
('2000000001','工行','张飞',0,0
)
insert into Trade values('2000000001',GETDATE(),1000,'存钱')
insert into Trade values('1000000001',GETDATE(),1000,'取钱')