数据库基本操作与触发器

use master
go
/*----检测某个数据库是否已经存在-----*/
if exists(select * from sysdatabases where name='BankCarInfo')
drop database BankCarInfo
/*--新建数据库*/
create database BankCarInfo
--数据库文件具体描述
on primary
(
name='BankCarInfo_data',
filename='d:/kaoshi/BankCarInfo_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
--日志文件的具体描述
log on
(
name='BankCarInfo_log',
filename='d:/kaoshi/BankCarInfo_log.ldf',
size=2mb,
filegrowth=1mb
)
go

use BankCarInfo
go
if exists(select * from sysobjects where name='bank' )
drop table bank

if exists(select * from sysobjects where name='transInfo')
drop table transInfo

go

/*---------帐户信息表--------*/
create table bank --帐户信息
(
customerName char(8) not null, --顾客姓名
cardID char(10) not null, --卡号
currentMoney money not null  --当前余额
)
go

/*-------交易信息表------*/
create table transInfo
(
cardID CHAR(10) NOT NULL,--卡号
transType char(4) not null,--交易类型(存入/支取)
transMoney money not null,--交易金额
transDate datetime not null --交易日期
)
go

/*---------添加约束：帐户余额不能少于1元，交易日期默认为当天日期-----------*/
alter table bank
add constraint ck_currentMoney check(currentMoney>=1)

alter table transInfo
add constraint df_transDate default (getdate()) for transDate

go
select * from bank
go
select * from transInfo
go
/*--------插入测试数据-----*/
insert into bank(customerName,cardID,currentMoney) values('张三','10010101',5000)
insert into bank(customerName,cardID,currentMoney) values('李四','20020202',1000000)

/*-------插入测试数据-------*/
insert into transInfo(cardID,transType,transMoney)values('10010101','支取',200)
/*--触发器实例-*/
use BankCarInfo
go
/*---检测是否存在：触发器放在系统表中sysobjects--------*/
if exists(select name from sysobjects where name='trig_transInfo')
drop trigger trig_transInfo
go
create trigger trig_transInfo
on transInfo
for insert
as
/*------定义变量：用于临时存放插入的卡号、交易类型、交易金额----*/
declare @type char(4),@outMoney money
declare @myCardId char(10),@balance money
/*-------从inserted临时表中获取插入的记录行信息：包括交易类型、卡号、交易金额------*/
select @type=transType,@outMoney=transMoney,@myCardID=cardID from inserted
/*--根据交易类型是支取/存入，减少域增加帐户表(bank)中对应卡号的余额--*/

if(@type='支取')
update bank set currentMoney=currentMoney-@outMoney
   where cardID=@myCardID
else
update bank set currentMoney=currentMoney+@outMoney
   where cardID=@myCardID

/*--显示交易金额及余额-*/
print '交易成功！交易金额:'+convert(varchar(20),@outMoney)
select @balance=currentMoney from bank where cardID=@myCardID
print '卡号'+@myCardID+'余额:'+convert(varchar(20),@balance)
go

/*测试触发器插入测试数据：张三取钱200，李四存钱50000*/
--delete from transInfo
set nocount on --不显示T-SQL语语影响的记录行数
insert into transInfo(cardID,transType,transMoney) values('10010101','支取',200)
insert into transInfo(cardID,transType,transMoney) values('20020202','存钱',50000)
--查看结果
select * from bank
select * from transInfo
delete from bank where customerName='张三'
/*--删除触发器--*/
go
if exists(select name from sysobjects where name='trig_delete_transInfo')
drop trigger trig_delete_transInfo
go
create trigger trig_delete_transInfo
on transInfo
for delete
as
print '开始备份数据库，请稍后......'
if not exists(select * from sysobjects where name='backupTable')
   select * into backupTable from deleted--从deleted表中获取被删除的数据
else
    insert into backupTable select * from deleted
print '备份数据成功,备份表中的数据为:'
select * from backupTable
go

/*-测试触发器：删除数据--*/
set nocount on--不显示T-SQL语句影响的记录行数
delete transInfo
print'交易信息表中的数据：'
select * from transInfo

/*--检测是否存在：触发器存放在系统表sysobjects中---*/
if exists(select name from sysobjects where name='trig_update_bank')
drop trigger trig_update_bank
go

/*--创建update触发器：在表transInfo上创建更新触发器-*/
create trigger trig_update_bank
on bank
for update
as
declare @beforMoney money ,@afterMoney money --定义变量
select @beforMoney=currentMoney form deleted  --获取交易前的余额
select @afterMoney=currentMoney form inserted
if abs(@afterMoney-@beforMoney)>20000 --交易金额是否>20000
  begin
 print'交易金额:'
 +convert(varchar(8),abs(@afterMoney-@beforMoney))
 raiserror ('每笔交易不能超过2万元，交易失败',16,1)
 rollback transaction
 end
go
/*--测试触发器：修改余额--*/
set nocount on
update bank set currentMoney=currentMoney+25000 where cardID='10010101'--凭存折
go
insert into transInfo(CardID,transType,transMoney) values('20020202','支取',30000)--凭卡
insert into transInfo(CardID,transType,transMoney) values('20020202','存入',5000)--凭卡
go
print '帐户信息表的数据'
select * from bank
print '交易信息表中的数据'
select * from transInfo
go