银行系统sql触发器代码

时间：2008-06-30 来源：ziyouwawa

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)
if exists (select * from sysdatabases where name ='BankCard')
drop database BankCard
go
create database BankCard/*创建数据库*/
use BankCard if exists (select * from sysobjects where name='Bank')
drop table Bank
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 (10)
)
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
go
create trigger trig_Bank
on Trade /*trig_Bank为触发器的名称，Bank为表的名称*/
for insert /*可以为inser、delete、select、update*/
as
--定义变量，用于临时存储
declare @type char(4)
declare @outMoney money
declare @balance money
declare @myCardID int
/*从临时表中获取插入的记录：类型、卡号、金额*/
select @type=TradeType,@outMoney=TradeMoney,@myCardID=CardID from inserted
if(@type='支取')
update Bank set Payout =Payout+@outMoney where cardID=@myCardID
go
select * from Bank
select *from trade sa登陆失败 1、属性—安全性—将SQL server 改为 SQL server 和window server 2、打开安全性双击登录
双击sa把密码改成sa