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

14 Aug 2006 2:01 PM

dba_222

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_trigger
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!

14 Aug 2006 3:19 PM

Michael Hotek

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(<columname>) clause.

--
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com

<dba_***@yahoo.com> wrote in message

Show quote

news:1155564108.206051.326610@74g2000cwt.googlegroups.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_trigger
> 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!
>

14 Aug 2006 10:24 PM

Erland Sommarskog

(dba_***@yahoo.com) writes:
Show quote

> 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_trigger
> 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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx