銀行sql觸發器代碼
觸發器是一種特殊的存儲過程，在用戶試圖對指定的表執行指定的數據修改語句時自動執行。下文為大家介紹銀行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
【編輯推薦】
