create tri_test on t1 for update as if update(f1) begin --新插入的值在inserted里 --原来的值可以从deleted里取到 end
--示例create trigger tr_update on t1 for update as if update(f1) begin select id=identity(int,1,1),f1 into #i from inserted select id=identity(int,1,1),f1 into #d from deleted --显示变化情况 select 更新前=d.f1,更新后=i.f1,变化值=i.f1-d.f1 from #i i join #d d on i.id=d.id --将满足条件的进行处理 update .... set 另一字段=i.f1 from #i i join #d d on i.id=d.id where i.f1-d.f1>=20 end go
如果满足条件,变化超过20就取消update操作呢???
create trigger tr_update on t1 for update as if update(f1) begin select id=identity(int,1,1),f1 into #i from inserted select id=identity(int,1,1),f1 into #d from deleted if exists(select 1 from #i i join #d d on i.id=d.id where i.f1-d.f1>=20) rollback tran end go
for update
as
if update(f1)
begin
--新插入的值在inserted里
--原来的值可以从deleted里取到
end
for update
as
if update(f1)
begin
select id=identity(int,1,1),f1 into #i from inserted
select id=identity(int,1,1),f1 into #d from deleted --显示变化情况
select 更新前=d.f1,更新后=i.f1,变化值=i.f1-d.f1
from #i i join #d d on i.id=d.id --将满足条件的进行处理
update .... set 另一字段=i.f1
from #i i join #d d on i.id=d.id
where i.f1-d.f1>=20
end
go
for update
as
if update(f1)
begin
select id=identity(int,1,1),f1 into #i from inserted
select id=identity(int,1,1),f1 into #d from deleted if exists(select 1
from #i i join #d d on i.id=d.id
where i.f1-d.f1>=20)
rollback tran
end
go