27,579
社区成员
发帖
与我相关
我的任务
分享
------------------------
/******
建表
******/
------------------------
if object_id('p') is not null
drop table p
go
create table p(
name nvarchar(20) not null,
ver nvarchar(20) not null,
active bit null,
constraint [pk_p] primary key(name,ver)
)
go
------------------------
/******
建触发器
******/
------------------------
create trigger p_upd
on p
for update
as
if update(active) and (select count(1) from inserted)>1
begin
rollback tran;
raiserror('每次只能更新一条',11,1);
return;
end
if update(active)
begin
update p set active=0
from inserted i
where i.name=p.name and i.ver<>p.ver
and isnull(i.active,0)=1;
end
go
------------------------
/******
测试数据
******/
------------------------
insert into p values('p1','a',0);
insert into p values('p1','b',1);
insert into p values('p1','c',0);
go
------------------------
if update(active) and (select count(1) from inserted)>1
begin
rollback tran;
raiserror('每次只能更新一条',11,1);
return;
end
if update(active)
begin
update p set active=0
from inserted i
where i.name=p.name and i.ver=p.ver
;
end
update p set name='p2',active=1 where ver='c'
--or
update p set name='p3',active=1 where ver='a'
if update(active)
begin
update p set active=0
from inserted i
where i.name=p.name and i.ver=p.ver
and isnull(i.active,0)=1 ;
end
第一个问题
update p set name='p2',ver='f',active=1 where name='p1' and ver='c'
select * from p
--下面这样的算不算?
/*
name ver active
-------------------- -------------------- ------
p1 a 0
p1 b 1
p2 f 1
(3 行受影响)
*/
--如果对,要避免的话 把 and isnull(i.active,0)=1;
--去掉,因为一次只能更新一行,那么它跟定的把0的更新成1才会出现两行
--所以你只需要把更新的那一行在更新一遍(更新为0),就行了。
UPDATE P SET active = 1
WHERE ver <> 'B'
insert into p SELECT 'p1','f',1
UNION ALL SELECT 'p1','G',1
------------------------
/******
建触发器
******/
------------------------
ALTER trigger p_upd
on p
for update,INSERT
as
if update(active)
begin
update p set active=0
from inserted i
where i.name=p.name and i.ver<>p.ver
and isnull(i.active,0)=1
AND NOT EXISTS (
SELECT 1 FROM inserted i1
WHERE I1.NAME = I.NAME AND i1.ver>I.ver
)
end
go
------------------------
/******
建触发器
******/
------------------------
ALTER trigger p_upd
on p
for update,INSERT
as
if update(active) and (select count(1) from inserted)>1
begin
rollback tran;
raiserror('每次只能更新一条',11,1);
return;
end
if update(active)
begin
update p set active=0
from inserted i
where i.name=p.name and i.ver<>p.ver
and isnull(i.active,0)=1;
end
go