ACCP银行ATM机项目

use master go if exists (select * from sysdatabases where name = 'bank') drop database bank create database bank go use bank go if exists (select * from sysobjects where name = 'bankcard') drop table bankcard go create table BankCard ( CardID char(10) not null, BankName varchar(20) not null, UserName varchar(20) not null, Deposit money, Payout money ) go alter table BankCard add constraint PK_CardID primary key (CardID), constraint DF_Deposit default(0) for Deposit, constraint DF_Payout default(0) for Payout go if exists (select * from sysobjects where name = 'Trade') drop table Trade go create table Trade ( TradeID bigint identity(1,1), CardID char(10), TradeDate datetime not null, TradeMoney money, TradeType char(4) ) go alter table Trade add constraint FK_CardID foreign key (CardID) references BankCard(CardID), constraint CK_TradeMoney check(TradeMoney > 0), constraint CK_TradeType check(TradeType = '存钱' or TradeType = '取钱') go create trigger trigger_Trade on Trade for insert as declare @tradetype char(4),@trademoney money,@tradecardid char(10) select @tradetype = TradeType,@trademoney = TradeMoney,@tradecardid = CardID from inserted if (@tradetype = '存钱') begin update BankCard set Deposit = Deposit + @trademoney where CardID = @tradeCardID end else begin update BankCard set Payout = @trademoney where CardID = @tradeCardID end go insert into BankCard(cardid,bankname,username) values('101','招行','关羽') insert into BankCard(cardid,bankname,username) values('201','工行','张飞') select * from bankcard select * from trade --where tradedate like '2008-02-05%'