银行系统sql触发器代码。

if exists (select * from sysdatabases where name ='BankCard')

drop database BankCard

go

create database BankCard/*创建数据库*/

use BankCard

create table Bank

(

CardID char(10) primary key,

BankName varchar(20)not null,

UserName varchar(20)not null,

Depisit money not null,

Payout money not null

)

alter  table Bank add constraint DF_Depisit default (0) for Depisit

alter table Bank add constraint DF_Payout default (0)for Payout

if exists (select * from sysobjects where name='trade')

drop table trade

create table Trade

(

TradeID int identity(1,1),

CardID char(10),

TradeDate DateTime  not null,

TradeMoney Money,

TradeType char

)

alter table Trade add constraint PK_TradeID primary key(TradeID),

alter table Trade add constraint FK_CardID foreign key (CardID) references Bank(CardID),

alter table Trade add constraint CK_TradeMoney check (TradeMoney>0)



/*触发器（trigger）*/

if exists (select * from sysobjects where name ='trig_Bank')

drop trigger trig_Bank



create trigger trig_Bank

on Trade /*trig_Bank为触发器的名称，Bank为表的名称*/

for  insert/*可以为inser、delete、select、update*/

as

declare @type char(4),@balance money,@outMoney money,@myCardID int

/*定义变量，用于临时存储

/*从临时表中获取插入的记录：类型、卡号、金额*/

select @type=TradeType,@outMoney=TradeMoney,@myCardID=CardID from  inserted

if(@type='支取')

update bank set Payout =Payout-@outMoney where cardID=@myCardID

insert into trans(cardID,transType,transMoney)

values('10001000',支取,200)

 

go

select * from Bank

select *from trade