SQL Server trigger that fires only on update of certain field?

[dba_222@yahoo.com]

Dear Experts,


I'm an Oracle guy, who is being given more SQL Server assignments
lately.

I've been looking for things on the web about this, but I can't
anything so far.

In Oracle, I you can create a trigger on a table that -only- fires if
certain fields are updated.

create or replace trigger trg_some_trigge r
BEFORE insert
OF some_field1, some_field2
on tbl_some_table
for each row

....



Is this also possible in SQL Server?
What is the syntax please?


Thanks a lot!

[Michael Hotek]

Re: SQL Server trigger that fires only on update of certain field?

Not directly. SQL Server is going to fire the trigger for the operation.
It doesn't conditionally fire it based on a column being changed. You can
accomplish this within your code by using the IF UPDATE(<columna me>) clause.

--
Mike Hotek
MHS Enterprises, Inc

http://www.mssqlserver.com

<dba_222@yahoo. comwrote in message
news:1155564108 .206051.326610@ 74g2000cwt.goog legroups.com...

Dear Experts,
>
>
I'm an Oracle guy, who is being given more SQL Server assignments
lately.
>
I've been looking for things on the web about this, but I can't
anything so far.
>
In Oracle, I you can create a trigger on a table that -only- fires if
certain fields are updated.
>
create or replace trigger trg_some_trigge r
BEFORE insert
OF some_field1, some_field2
on tbl_some_table
for each row
>
...
>
>
>
Is this also possible in SQL Server?
What is the syntax please?
>
>
Thanks a lot!
>

[Erland Sommarskog]

Re: SQL Server trigger that fires only on update of certain field?

(dba_222@yahoo. com) writes:

I'm an Oracle guy, who is being given more SQL Server assignments
lately.
>
I've been looking for things on the web about this, but I can't
anything so far.
>
In Oracle, I you can create a trigger on a table that -only- fires if
certain fields are updated.
>
create or replace trigger trg_some_trigge r
BEFORE insert
OF some_field1, some_field2
on tbl_some_table
for each row
>
...
>

As Mike said, the best you can do is to check in the trigger whether
a columns was updated:

IF UPDATE(col)
BEGIN
-- Do stuff
END

But keep in mind that this not tell you whether any values in the
column were changed, only that it was mention in the SET clause of
an UPDATE statement. And for INSERT the condition is alwauys tru.

Also keep in mind that in SQL Server a trigger fires once per
statement, not once per row.

And, finally, there are no BEFORE triggers in SQL Server. Only INSTEAD OF
triggers and AFTER triggers.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx