在两个表之间创建触发器

创建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)
)

创建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,'取钱')