在两个表之间创建触发器

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