今天做一个存储过程时出了一个这样的问题,请各位帅哥美女们帮忙解答一下好吗?万分感谢!有类似以下结构的三张表
用户表:
create table users
( id int identity(1,1) primary key ,
username nchar(15) not null Unique,
[password] nchar(25) not null ,
)
角色表:
create table roles
( id int identity(1,1) primary key ,
rolename nchar(15) not null Unique,
note nchar(100)
)
用户—角色表
create table user_role
( userid int foreign key references users(id),
roleid int foreign key feferences users(id)
)
角色表中有三条记录如下:
insert into roles values('admin','超级管理员')
insert into roles values('user','普通用户')
insert into roles values('guest','来宾用户')
新建用户存储过程:
NewUser:
create procedure NewUser
(
@username nchar(15),
@password nchar(25),
)
as
begin
begin tran add_user
insert into users values(@username,@password)
insert into user_role values(scope_identity(),2)
insert into user_role values(scope_identity(),3)
commit tran add_user
end
以上存储过程编译通过,可是我发现执行的时候,事务不起作用,
如:
exec NewUser 'cccc','154645564'
结果如下:
插入一条新记录 影响1行
插入一条新记录 影响1行
插入一条新记录 影响1行
我再把上面的再执行一次
exec NewUser 'cccc','154645564'
结果如下:
违反了UK 唯一性约束,
插入一条新记录 影响1行
插入一条新记录 影响1行
以前我一直认为,加入事务后,有一条语句出错,后面的应该不执行才对啊。还有应该rollback才对啊。但是事实告诉我,我的理解出了偏差,请各位大哥,大姐帮
忙解答一下,我是什么地方理解错了,还有上面的存储过程应该怎样调整才对!
用户表:
create table users
( id int identity(1,1) primary key ,
username nchar(15) not null Unique,
[password] nchar(25) not null ,
)
角色表:
create table roles
( id int identity(1,1) primary key ,
rolename nchar(15) not null Unique,
note nchar(100)
)
用户—角色表
create table user_role
( userid int foreign key references users(id),
roleid int foreign key feferences users(id)
)
角色表中有三条记录如下:
insert into roles values('admin','超级管理员')
insert into roles values('user','普通用户')
insert into roles values('guest','来宾用户')
新建用户存储过程:
NewUser:
create procedure NewUser
(
@username nchar(15),
@password nchar(25),
)
as
begin
begin tran add_user
insert into users values(@username,@password)
insert into user_role values(scope_identity(),2)
insert into user_role values(scope_identity(),3)
commit tran add_user
end
以上存储过程编译通过,可是我发现执行的时候,事务不起作用,
如:
exec NewUser 'cccc','154645564'
结果如下:
插入一条新记录 影响1行
插入一条新记录 影响1行
插入一条新记录 影响1行
我再把上面的再执行一次
exec NewUser 'cccc','154645564'
结果如下:
违反了UK 唯一性约束,
插入一条新记录 影响1行
插入一条新记录 影响1行
以前我一直认为,加入事务后,有一条语句出错,后面的应该不执行才对啊。还有应该rollback才对啊。但是事实告诉我,我的理解出了偏差,请各位大哥,大姐帮
忙解答一下,我是什么地方理解错了,还有上面的存储过程应该怎样调整才对!
(
@username nchar(15),
@password nchar(25),--here
)
编译能通过?
(
@username nchar(15),
@password nchar(25),
)
as
begin
begin tran add_user
insert into users values(@username,@password)
if @@error <> 0 goto err:
insert into user_role values(scope_identity(),2)
if @@error <> 0 goto err:
insert into user_role values(scope_identity(),3)
if @@error <> 0 goto err:
commit tran add_user
return
err:
rollback tran add_user
end
create procedure NewUser
(
@username nchar(15),
@password nchar(25),
)
as
begin
set xact_abort on --郝老师:SQL server2000 默认把xact_abort设置成off要实现自动回滚,要设置成off才行。
begin tran add_user
insert into users values(@username,@password)
insert into user_role values(scope_identity(),2)
insert into user_role values(scope_identity(),3)
commit tran add_user
end 在这里再次感谢几位大哥的义助
指定当 Transact-SQL 语句产生运行时错误时,Microsoft® SQL Server™ 是否自动回滚当前事务。语法
SET XACT_ABORT { ON | OFF }注释
当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL 语句产生运行时错误,整个事务将终止并回滚。为 OFF 时,只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。编译错误(如语法错误)不受 SET XACT_ABORT 的影响。对于大多数 OLE DB 提供程序(包括 SQL Server),隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。有关更多信息,请参见分布式查询和分布式事务。 SET XACT_ABORT 的设置是在执行或运行时设置,而不是在分析时设置。
create table user_role
( userid int foreign key references users(id),
roleid int foreign key feferences users(id)
)create table user_role
( userid int foreign key references users(id),
roleid int foreign key references users(id)
)