各位朋友,存储过程如下:
alter procedure insert_update
@emp_id,@card_id,@emp_name,@insert_update,@return varchar(100) outputas
if @insert_update='update'
begin
update 表 set card_id=@card_id, emp_name=@emp_name whereemp_id=@emp_id
/*
问题1:想在更新记录操作之后,才做一个判断,如果
select emp_name from employee where emp_name=@emp_name group by emp_name having count(emp_name)>1的话,就提示用户“姓名不能重复!”,然后将刚才的更新操作取消,而且,这个判断必须要在更新记录之后才做,而不是更新记录之前做,请问这个该如何实现,是不是必需要用事务,如果要用事务的话,该怎样写,请各位朋友赐教,谢谢!!!*/
end
alter procedure insert_update
@emp_id,@card_id,@emp_name,@insert_update,@return varchar(100) outputas
if @insert_update='update'
begin
update 表 set card_id=@card_id, emp_name=@emp_name whereemp_id=@emp_id
/*
问题1:想在更新记录操作之后,才做一个判断,如果
select emp_name from employee where emp_name=@emp_name group by emp_name having count(emp_name)>1的话,就提示用户“姓名不能重复!”,然后将刚才的更新操作取消,而且,这个判断必须要在更新记录之后才做,而不是更新记录之前做,请问这个该如何实现,是不是必需要用事务,如果要用事务的话,该怎样写,请各位朋友赐教,谢谢!!!*/
end
@emp_id,@card_id,@emp_name,@insert_update,@return varchar(100) outputas
if @insert_update='update'
begin
begin transaction
update 表 set card_id=@card_id, emp_name=@emp_name whereemp_id=@emp_id
if exists (select 1 from 表 group by emp_name having count(emp_name)>1)
rollback transaction
else
commit transaction
end
@emp_id,@card_id,@emp_name,@insert_update,@return varchar(100) outputas
if @insert_update='update'
begin
begin transaction
update 表 set card_id=@card_id, emp_name=@emp_name whereemp_id=@emp_id
if exists (select 1 from 表 group by emp_name having count(emp_name)>1)
begin
rollback transaction
set @return='姓名不能重复!'
end
else
commit transaction
end
@emp_id,@card_id,@emp_name,@insert_update,@return varchar(100) outputas
if @insert_update='update'
begin
begin transaction
update 表 set card_id=@card_id, emp_name=@emp_name whereemp_id=@emp_id
if exists (select 1 from 表 group by emp_name having count(emp_name)>1)
begin
rollback transaction
set @return='姓名不能重复!'
return
end
else
commit transaction
end
这真是一个致命的漏洞,楼主是否改变初衷?
create table test
(
id int primary key,
name varchar(20)
)create proc update_test
@id int,
@name varchar(20),
@return varchar(20) output
as
begin
begin tran
update test set name=@name where id=@id
if((select count(*) from test where name=@name)>1)
begin
rollback tran
set @return='姓名不能重复'
end
else
commit tran
end alter trigger autoCheck
on test
for update
as
begin
declare @name varchar(20)
declare @id int
select @id=id,@name=name from inserted
if((select count(*) from test where name=@name)>0)
begin
delete from test where id=@id
insert into test select * from deleted
end
end
再次提醒:如果不通过这个存储过程,而是手工生成一条emp_name重复的数据,那么通过这个存储过程将永远更新不了数据,直道将这条重复的emp_name被删除或更新为不重复。
这样只要有重复记录,事务就会回滚。if exists (select 1 from 表 where emp_name=@emp_name group by emp_name having count(emp_name)>1)
--这样就OK了,抱歉!!
方法一:唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (emp_name)
CREATE UNIQUE INDEX 索引名 ON 表名 (card_id)方法二:唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE NONCLUSTERED (emp_name)
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE NONCLUSTERED (card_id)
begin begin tran
update employee set card_id=@card_id,emp_name=@emp_name
if exists (select * from employee where card_id=@card_id group by card_id having count(card_id)>1)
begin
rollback transaction
set @message='员工卡号不能重复!'
return
end
else
commit tran
if @@rowcount>0
begin
set @message='更新记录成功!'
return
end
else
begin
set @message='更新记录失败!'
return
end
end
beginbegin tran
update employee set card_id=@card_id,emp_name=@emp_name
set @count=@@rowcount
if exists (select * from employee where card_id=@card_id group by card_id having count(card_id)>1)
begin
rollback transaction
set @message='员工卡号不能重复!'
return
end
else
commit tran
if @count>0
begin
set @message='更新记录成功!'
return
end
else
begin
set @message='更新记录失败!'
return
endend