473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
2 20715
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
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Nick Pritchard | last post by:
Well, there are a couple ways you could do that. You could write a windows service with a System.Timers.Timer object, that fires an event ever 15 minutes. Or, you could use a trigger on the...
6
by: Dave C. | last post by:
Hello, I have created the following trigger: CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE AS DECLARE @foobar varchar(100) SELECT @foobar= foobar FROM inserted IF (...
1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
3
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the...
7
by: Lucio Chiessi | last post by:
Hi for all on this... I'm using MS SQL Server 7.0 SP4 in some customers to store some data from an aplication developed by me. I created an trigger to run on update. When I run an update...
4
by: Alexander Pope | last post by:
I am using db2 udb v8.2 AIX I have created trigger, however I am not confident it meets industry standards. If I make it fail, I cant tell from the message where it is failing. what can I add to...
2
by: dbuchanan52 | last post by:
Hello, I am building an application for Windows Forms using. I am new to SQL Server 'Views'. Are the following correct understanding of their use? 1.) I believe a view can be referenced in a...
1
by: aj70000 | last post by:
hi, Here's the scenario 1) I am running a DTS job to fetch some rows from Oracle 2) The job populates the Table A as step 1 3) Then it fires a update statement which updates the rows in Table...
1
by: overlordqd | last post by:
hi all, i want to do something like that; if someone tries to update a table, the trigger will fire and add a new record that contains the field name, old value and new value. i have already...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.