觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，在用戶(hù)試圖對(duì)指定的表執(zhí)行指定的數(shù)據(jù)修改語(yǔ)句時(shí)自動(dòng)執(zhí)行。下文為大家介紹銀行sql觸發(fā)器代碼。

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

drop database BankCard

go

create database BankCard/*創(chuàng)建數(shù)據(jù)庫(kù)*/

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)

/*觸發(fā)器（trigger）*/

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

drop trigger trig_Bank

create trigger trig_Bank

on Trade /*trig_Bank為觸發(fā)器的名稱(chēng)，Bank為表的名稱(chēng)*/

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

as

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

/*定義變量，用于臨時(shí)存儲(chǔ)

/*從臨時(shí)表中獲取插入的記錄：類(lèi)型、卡號(hào)、金額*/

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

【編輯推薦】
 

1. 如何編寫(xiě)高效的數(shù)據(jù)庫(kù)代碼
2. mysql觸發(fā)器的三個(gè)狀態(tài)
3. 教您如何使用mysql觸發(fā)器