423,688 Members | 1,894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

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

P: n/a
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!

Aug 14 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
<db*****@yahoo.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.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!

Aug 14 '06 #2

P: n/a
(db*****@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_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, es****@sommarskog.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
Aug 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.