Trigger - Capture Field Changed

Question

Post reply

Trigger - Capture Field Changed

JuanBob

SSCertifiable

Points: 5059
More actions
January 10, 2007 at 3:44 pm

#118500

I need to write my trigger to determine the changes at the field level, not the row level. How can I determine which field is being modified?
In my example below, I'm trying to determine if the Contact field was updated.
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
SET QUOTED_IDENTIFIER OFF
SET ROWCOUNT ON
IF EXISTS
  (
  Select i.contact
  From Inserted i inner join tblContacts c on i.recId = c.recId
  Where i.contact <> c.contact
&nbsp
BEGIN
     ...
END

Also, as a side question, should my example use "with (nolock)", or is this not recommended within a trigger? I try to always avoid any kind of locks from within triggers.
Thanks in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

Try this article:

http://www.sqlservercentral.com/columnists/awarren/triggerscolumnsupdated.asp

Russel Loski, MCSE Business Intelligence, Data Platform

Vladan SSC-Insane Points: 22072 More actions · Answer 2

The linked article shows some useful info, but I'm not sure it applies to you. That depends on what you consider as "UPDATE".

For the sake of IF UPDATE(column) and IF (COLUMNS_UPDATED()), column is considered updated if a value was specified in the update statement. It does NOT check, whether the old and new value differ or are the same!!!

If you want to log changes to the column, then this doesn't help much and you have to rely on inserted.col <> deleted.col (be sure not to forget NULL treatment if the column is nullable). Since all the info is available in these tables, you don't need to join to the original table. And as long as you stick to the special trigger tables Inserted and Deleted, you don't have to bother about locks (no, I don't ever use WITH(NOLOCK) in triggers - you shouldn't allow dirty reads if you plan to write the result of trigger somewhere).

So, the condition in the trigger should be (if column "contact" does not allow NULLs)

IF EXISTS (select * from inserted i join deleted d on d.recID=i.recID where i.contact<>d.contact)

BEGIN.... END

If Contact can contain NULL, you need to specify the condition either using AND/OR construct with IS NULL/IS NOT NULL, or use something like ISNULL(d.contact,'')<>ISNULL(i.contact,'').

However, you don't really need to use IF EXISTS - you will have to include the logic into the inside of the BEGIN..END block as well anyway. Trigger has to work if multiple rows are updated at once, while in some of them value in the <column> changes, in some it doeasn't... so, why to repeat the same code twice? Just add joins to inserted and deleted and the conditions to the actual SQL (e.g. INSERT INTO logtable).

JuanBob SSCertifiable Points: 5059 More actions · Answer 3

Thank you both for the help! I was able to get the Column_updated() function to work. Though, I'm sure I would have trouble in a more complicated situation.

The application that updates the table only updates one field at a time. That is the nature of the application. There are integration pieces, however, that may update multiple columns in one transaction, so that is a concern as well. Since the table has 30+ columns, I need the trigger to be as efficient as possible so that it does not waste must processing time evaluating updates that are not the Contact field.

Vladan, you've provided very useful info that I will test with. I greatly appreciate it!