use studb
-- 如果该存储过程存在,则删除后再创建
if exists (select * from sysobjects where name = 'nopasscvb')
drop proc nopasscvb
go
-- 创建存储过程
CREATE PROC nopasscvb
@notpassSum int output , -- 返回不及格的人数
@writtenPass int = 60 , -- 参数1 笔试及格线
@labPass int = 60 -- 参数2 机试及格线
AS -- AS 后写的东西相当于JAVA中的方法体
if not( @writtenPass between 0 and 100
and @labPass between 0 and 100 )
begin
raiserror('及格线分数错误,必须在0 ~ 100 之间' , 16 , 1 )
return
end
declare @writtenAvg float -- 笔试平均分
declare @labAvg float -- 机试平均分
select @writtenAvg=avg(writtenExam),@labAvg=avg(labExam)
from stuMarks
print '笔试平均分:' + convert(varchar(5),@writtenAvg)
print '机试平均分:' + convert(varchar(5),@labAvg)
if (@writtenAvg > 70 and @labAvg > 70)
print '本班考试成绩:优秀'
else
print '本班考试成绩:较差'
print '-------------------------------------'
print ' 没有及格的人员名单如下:'
select stuname,a.stuno,writtenExam,labExam from stuInfo a,stuMarks b
where a.stuno = b.stuno and
(b.writtenExam < @writtenPass or b.labExam < @labPass)
select @notpassSum=count(*) from stumarks where writtenExam < @writtenPass or labExam < @labPass
go
declare @sum int
exec nopasscvb @sum output,170,170
print @sum
print @@error
declare @writtenPass int
declare @labPass int
set @writtenPass = 11
set @labPass = 600
if not ( @writtenPass between 0 and 100
and @labPass between 0 and 100 )
print '错误'
else
print '正确'
use bank
create database newbank
go
use newbank
go
create table bank (
name varchar(20),
card varchar(8) primary key,
money money check(money >= 1)
)
insert into bank values('张三','10010001',1000)
insert into bank values('李四','10010002',1)
select * from bank
create table transinfo(
transDate datetime ,
card varchar(8) foreign key references bank(card),
transType varchar(4) check(transType in('存入','取出')),
transMoney money
)
select * from bank
select * from transinfo
insert into transinfo values(getdate(),'10010001','存入',1000)
if exists (select * from sysobjects where name = 'trigger_1')
drop trigger trigger_1
go
--通过触发器完成
create trigger trigger_1
on transinfo
for insert
as
-- 从inserted 表中,取出存入的金额 用于修改bank表中用户的余额
declare @money money -- 金额
declare @card varchar(8) -- 卡号
declare @transType varchar(4) -- 交易类型
select @money = transmoney,@card = card,@transType = transType from inserted
print '交易金额:'+convert(varchar,@money)
print '交易卡号:'+@card
print '交易类型:'+@transType
if @transType = '存入'
update bank set money = money + @money where card = @card
else
update bank set money = money - @money where card = @card
go
insert into transinfo values(getdate(),'10010001','存入',1888)
insert into transinfo values(getdate(),'10010001','取出',1999999)
select * from bank
if exists (select * from sysobjects where name = 'trigger_1')
drop trigger trigger_1
go
--通过触发器完成
create trigger trigger_1
on transinfo
for insert
as
-- 从inserted 表中,取出存入的金额 用于修改bank表中用户的余额
declare @money money -- 金额
declare @card varchar(8) -- 卡号
declare @transType varchar(4) -- 交易类型
declare @userMoney money -- 用户交易前的余额
select @money = transmoney,@card = card,@transType = transType from inserted
select @userMoney = money from bank where card = @card
print '交易金额:'+convert(varchar,@money)
print '交易卡号:'+@card
print '交易类型:'+@transType
if @transType = '存入'
update bank set money = money + @money where card = @card
else
begin
if @userMoney >= @money + 1
update bank set money = money - @money where card = @card
else
begin
raiserror('用户余额不足',16,1)
rollback transaction
end
end
go
select * from transinfo
-- 删除触发器
create trigger trigger_2
on transinfo
for delete
as -- 如果删除数据,则将删除的数据存入backtable表中
if exists( select * from sysobjects where name = 'backtable')
insert into backtable select * from deleted
else
select * into backtable from deleted
go
select * from transinfo
select * from backtable
delete transinfo where transmoney=1999
create trigger trigger_3
on bank
for update
as
declare @beforeMoney money
declare @afterMoney money
select @beforeMoney = money from deleted
select @afterMoney = money from inserted
if abs(@afterMoney - @beforeMoney) > 20000
begin
raiserror('每笔交易金额不得大于20000元整,交易失败',16,1)
rollback transaction
end
go
insert into transinfo values(getdate(),'10010001','取出',29919)
-- 如果该存储过程存在,则删除后再创建
if exists (select * from sysobjects where name = 'nopasscvb')
drop proc nopasscvb
go
-- 创建存储过程
CREATE PROC nopasscvb
@notpassSum int output , -- 返回不及格的人数
@writtenPass int = 60 , -- 参数1 笔试及格线
@labPass int = 60 -- 参数2 机试及格线
AS -- AS 后写的东西相当于JAVA中的方法体
if not( @writtenPass between 0 and 100
and @labPass between 0 and 100 )
begin
raiserror('及格线分数错误,必须在0 ~ 100 之间' , 16 , 1 )
return
end
declare @writtenAvg float -- 笔试平均分
declare @labAvg float -- 机试平均分
select @writtenAvg=avg(writtenExam),@labAvg=avg(labExam)
from stuMarks
print '笔试平均分:' + convert(varchar(5),@writtenAvg)
print '机试平均分:' + convert(varchar(5),@labAvg)
if (@writtenAvg > 70 and @labAvg > 70)
print '本班考试成绩:优秀'
else
print '本班考试成绩:较差'
print '-------------------------------------'
print ' 没有及格的人员名单如下:'
select stuname,a.stuno,writtenExam,labExam from stuInfo a,stuMarks b
where a.stuno = b.stuno and
(b.writtenExam < @writtenPass or b.labExam < @labPass)
select @notpassSum=count(*) from stumarks where writtenExam < @writtenPass or labExam < @labPass
go
declare @sum int
exec nopasscvb @sum output,170,170
print @sum
print @@error
declare @writtenPass int
declare @labPass int
set @writtenPass = 11
set @labPass = 600
if not ( @writtenPass between 0 and 100
and @labPass between 0 and 100 )
print '错误'
else
print '正确'
use bank
create database newbank
go
use newbank
go
create table bank (
name varchar(20),
card varchar(8) primary key,
money money check(money >= 1)
)
insert into bank values('张三','10010001',1000)
insert into bank values('李四','10010002',1)
select * from bank
create table transinfo(
transDate datetime ,
card varchar(8) foreign key references bank(card),
transType varchar(4) check(transType in('存入','取出')),
transMoney money
)
select * from bank
select * from transinfo
insert into transinfo values(getdate(),'10010001','存入',1000)
if exists (select * from sysobjects where name = 'trigger_1')
drop trigger trigger_1
go
--通过触发器完成
create trigger trigger_1
on transinfo
for insert
as
-- 从inserted 表中,取出存入的金额 用于修改bank表中用户的余额
declare @money money -- 金额
declare @card varchar(8) -- 卡号
declare @transType varchar(4) -- 交易类型
select @money = transmoney,@card = card,@transType = transType from inserted
print '交易金额:'+convert(varchar,@money)
print '交易卡号:'+@card
print '交易类型:'+@transType
if @transType = '存入'
update bank set money = money + @money where card = @card
else
update bank set money = money - @money where card = @card
go
insert into transinfo values(getdate(),'10010001','存入',1888)
insert into transinfo values(getdate(),'10010001','取出',1999999)
select * from bank
if exists (select * from sysobjects where name = 'trigger_1')
drop trigger trigger_1
go
--通过触发器完成
create trigger trigger_1
on transinfo
for insert
as
-- 从inserted 表中,取出存入的金额 用于修改bank表中用户的余额
declare @money money -- 金额
declare @card varchar(8) -- 卡号
declare @transType varchar(4) -- 交易类型
declare @userMoney money -- 用户交易前的余额
select @money = transmoney,@card = card,@transType = transType from inserted
select @userMoney = money from bank where card = @card
print '交易金额:'+convert(varchar,@money)
print '交易卡号:'+@card
print '交易类型:'+@transType
if @transType = '存入'
update bank set money = money + @money where card = @card
else
begin
if @userMoney >= @money + 1
update bank set money = money - @money where card = @card
else
begin
raiserror('用户余额不足',16,1)
rollback transaction
end
end
go
select * from transinfo
-- 删除触发器
create trigger trigger_2
on transinfo
for delete
as -- 如果删除数据,则将删除的数据存入backtable表中
if exists( select * from sysobjects where name = 'backtable')
insert into backtable select * from deleted
else
select * into backtable from deleted
go
select * from transinfo
select * from backtable
delete transinfo where transmoney=1999
create trigger trigger_3
on bank
for update
as
declare @beforeMoney money
declare @afterMoney money
select @beforeMoney = money from deleted
select @afterMoney = money from inserted
if abs(@afterMoney - @beforeMoney) > 20000
begin
raiserror('每笔交易金额不得大于20000元整,交易失败',16,1)
rollback transaction
end
go
insert into transinfo values(getdate(),'10010001','取出',29919)