--
锁定记录,只允许单用户修改的例子:
-- 创建测试环境
-- 创建测试表--部门表
create table 部门(departmentid int ,name varchar ( 10 ))
-- 记录锁定表
create table lock(departmentid int ,dt datetime )
go
-- 因为函数中不可以用getdate,所以用个视图,得到当前时间
create view v_getdate as select dt = getdate ()
go
-- 创建自定义函数,判断记录是否锁定
create function f_chk( @departmentid int )
returns bit
as
begin
declare @re bit , @dt datetime
select @dt = dt from v_getdate
if exists ( select 1 from lock where departmentid = @departmentid
and datediff (minute,dt, @dt ) < 5 ) -- 锁的超时时间为5分钟
set @re = 1
else
set @re = 0
return ( @re )
end
go
-- 数据处理测试,操作记录3
if dbo.f_chk( 3 ) = 1
print ' 记录被锁定 '
else
begin
begin tran
insert into lock values ( 3 , getdate ())
update 部门 set name = ' A ' where departmentid = 3
delete from lock where departmentid = 3
commit tran
end
-- 删除测试环境
drop table 部门
drop view v_getdate
drop function f_chk
-- 创建测试环境
-- 创建测试表--部门表
create table 部门(departmentid int ,name varchar ( 10 ))
-- 记录锁定表
create table lock(departmentid int ,dt datetime )
go
-- 因为函数中不可以用getdate,所以用个视图,得到当前时间
create view v_getdate as select dt = getdate ()
go
-- 创建自定义函数,判断记录是否锁定
create function f_chk( @departmentid int )
returns bit
as
begin
declare @re bit , @dt datetime
select @dt = dt from v_getdate
if exists ( select 1 from lock where departmentid = @departmentid
and datediff (minute,dt, @dt ) < 5 ) -- 锁的超时时间为5分钟
set @re = 1
else
set @re = 0
return ( @re )
end
go
-- 数据处理测试,操作记录3
if dbo.f_chk( 3 ) = 1
print ' 记录被锁定 '
else
begin
begin tran
insert into lock values ( 3 , getdate ())
update 部门 set name = ' A ' where departmentid = 3
delete from lock where departmentid = 3
commit tran
end
-- 删除测试环境
drop table 部门
drop view v_getdate
drop function f_chk