if object_id('tbTest') is not null
drop table tbTest
GO
create table tbTest(id int,type int)
insert tbTest
select 1,1 union all
select 2,1 union all
select 3,5
GO
create trigger trg_insert on tbTest
for update
as
if update(type)
begin
if exists(select 1 from deleted as d inner join inserted as i
on d.id = i.id and d.type = 1 and i.type = 0)
raiserror('从1->0 !',16,1)
else
raiserror('未从1->0',16,1)
end
GO
----测试触发器
update tbTest set type = 0 where id = 1
update tbTest set type = 0 where id = 2
update tbTest set type = 0 where id = 3
-- 一般的写法是这样
create trigger tr_test on 你的表
for update -- 更新时触发
as
if @@rowcount = 0
return -- 无受影响的记录退出
if not update(type) -- 如果没有更新 type , 则退出
return
declare tb cursor local
for
select I.type, D.type
from inserted I, deleted D
where I.主键 = D.主键
open tb
declare @I_type int, @D_type int
fetch tb into @I_type, @D_type
while @@fetch_status = 0
begin
if @I_type = 1 and @D_type = 0 -- 从 0 -> 1
begin
你的处理
end
else
begin
你的处理
end
fetch tb into @I_type, @D_type
end
cose tb
deallocate tb