22,206
社区成员
发帖
与我相关
我的任务
分享
use testDB
go
if exists(select name from sysobjects where name = 'procTest1' and type='P')
drop procedure procTest1
go
create procedure procTest1
@menbName varchar(50)='',
@custName varchar(50)='',
@custAddress varchar(50)='',
@custPhone varchar(50)='',
@prodSerial varchar(50)='',
@prodFactName varchar(50)='',
@prodConfign varchar(50)='',
@prodModel varchar(50)=''
as
begin tran
insert into Men1(name) values(@menbName)
insert into Cust1(custName,custAddress,custPhone) values(@custName,@custAddress,@custPhone)
insert into Prod1(prodSerial,prodFactName,prodConfign,prodModel) values (@prodSerial,@prodFactName,@prodConfign,@prodModel)
if((select count(ID) from MsgError) > 0)
begin
select * from MsgError
-->rollback tran放置该位置后,MsgError表保存的错误信息被清空。
rollback tran
if exists(select msgStyle from MsgError where msgStyle = 'Men1')
return 1
else
return 2
-->rollback tran放置该位置后,执行报错。
/**
消息 266,级别 16,状态 2,过程 procTest1,第 20 行
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0,当前计数 = 1。
**/
end
else
commit tran
return @@error
go
if exists(select name from sysobjects where name='trigMen1Insert' and type='TR')
drop trigger trigMen1Insert
go
create trigger trigMen1Insert on Men1
instead of insert as
declare @业务员 varchar(50)
select @业务员=name from inserted
if (@业务员 in (select name from Men1))
insert into MsgError values((select count(ID)+1 from MsgError),'成员表已存在该名字。','Men1')
else
insert into Men1(name) values(@业务员)
go
if exists(select name from sysobjects where name = 'trigCust1Insert' and type='TR')
drop trigger trigCust1Insert
go
create trigger trigCust1Insert on Cust1
instead of insert as
declare @客户名 varchar(50)
select @客户名=custName from inserted
if (@客户名 in(select custName from Cust1))
insert into MsgError values((select count(ID) +1 from MsgError),'客户表已存在该用户名','Cust1')
else
insert into Cust1(custName,custAddress,custPhone) select custName,custAddress,custPhone from inserted
go
declare @megs int
exec @megs=procTest1 '王一','客户名','客户地址','13900201243','A234553','宝鸡机床','980TDb/DA98','TK36S'
select @megs as '返回值'
select * from MsgError
go
create procedure procTest1
@menbName varchar(50)='',
@custName varchar(50)='',
@custAddress varchar(50)='',
@custPhone varchar(50)='',
@prodSerial varchar(50)='',
@prodFactName varchar(50)='',
@prodConfign varchar(50)='',
@prodModel varchar(50)=''
as
begin tran
insert into Men1(name) values(@menbName)
insert into Cust1(custName,custAddress,custPhone) values(@custName,@custAddress,@custPhone)
insert into Prod1(prodSerial,prodFactName,prodConfign,prodModel) values (@prodSerial,@prodFactName,@prodConfign,@prodModel)
if (select count(ID) from MsgError) > 0
begin
select * from MsgError
if exists(select msgStyle from MsgError where msgStyle = 'Men1')
begin
rollback tran
return 1
end
else
begin
rollback tran
return 2
end
end
commit tran
return @@error