触发器判断字段是否更新？

[HardlyStudy]

要写一个触发器，判断字段F1是否被更新了，如果当F1有更新，再判断字段F1和字段F2里的值是否为空，如果都不为空时，将字段F3设置为F3=F1*F2。
这样的触发器如何写哦？

[gogodiy]

学习了

[pengxuan]

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 id int,
 f1 int,
 f2 int,
 f3 int
)
go 
insert into tb
select 1,1,2,2
go
if object_id('tr_tb','TR') is not null
   drop trigger tr_tb
go
create trigger tr_tb on tb after insert,update
as
  if Update(f1)
     update tb set f3=a.f1*a.f2 from tb a inner join inserted b on a.id=b.id
go

[suiyanpeng]

up...

[geniuswjt]

--try
create trigger trig_test on tb
for update
as
set nocount on

if update(F1)
begin
    if exists(select 1 from deleted where F1 is null and F2 is null)
    begin
        update tb set F3=(select F1*F2 from inserted)
    end
end

[--小F--]

[Quote=引用 3 楼 geniuswjt 的回复:]
SQL code

--try
create trigger trig_test on tb
for update
as
set nocount on

if update(F1)
begin
if exists(select 1 from deleted where F1 is null and F2 is null)
begin
upda……
[/Quote]
+

[HardlyStudy]

是不是View不能加触发器？只能给table加？
我写好了之后执行不了，总是提示：
The object 'View_AmountInformation_v' does not exist or is invalid for this operation.

[HardlyStudy]

MSDN上面有这样一句话：
A view can be referenced only by an INSTEAD OF trigger.
我不明白指得是什么意思？
是不是View上面不能加触发器呢？

[--小F--]

意思是可以使用 INSTEAD OF 触发器

[--小F--]

create trigger trig_test on tb
instead of  update
as
set nocount on

if update(F1)
begin
    if exists(select 1 from deleted where F1 is null and F2 is null)
    begin
        update tb set F3=(select F1*F2 from inserted)
    end
end

[huangdaxiang0726]

用 UPDATE 方法

[中国风]

[Quote=引用 1 楼 pengxuan 的回复:]

SQL code

if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int,
f1 int,
f2 int,
f3 int
)
go
insert into tb
select 1,1,2,2
go
if object_id('tr_tb','TR') is not null
……
[/Quote]
如果当F1有更新，再判断字段F1和字段F2里的值是否为空，如果都不为空时，将字段F3设置为F3=F1*F2。

create trigger tr_tb on tb after insert,update
as
  if Update(f1)
     update tb 
     set f3=a.f1*a.f2 from tb a inner join inserted b on a.id=b.id
     WHERE b.f1 IS NOT NULL AND b.f2 IS NOT null
go

[oolinyu]

不错学习了

[HardlyStudy]

谢谢各位！