Trigger at the field level

[Jomercat]

Hi all,

I am new to the SQL triggers.

I am designing an application which uses SQL as the backend. I want to be able to track changes to five fields of a table. Is it posible to use triggers at the field level. In other words, fire the trigger if either of the five fields is updated?

Thanks in advance.

Jose.

 

[SQLDenis]

yes
use if update(field)

check this out (

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html)

It will tell you about checking for NULL values etc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Jomercat]

SQLDenis,

Thanks for the prompt reply!

I will check the link.

Jose.

 

[SQLSister]

Yes.

Use the if update command.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Jomercat]

SQLSister,

Now If I want to log the individual changed values; in other words, field A (oldvale-newvalue), field B (oldvale-newvalue), etc. how would I write the statement. I got it to work for one field, but when I try to update more that one, I am getting an error.....

"Msg 8152, Level 16, State 14, Procedure trg_name_upt, Line 59
String or binary data would be truncated."

Here is what I am doing.

CREATE TRIGGER [trg_name_upt]
ON

FOR UPDATE AS

IF UPDATE(fieldA)

BEGIN
INSERT INTO logtable
(field1,
Field2,
SELECT del.oldvalue,
(SELECT ins.newvalue
FROM inserted ins)
FROM deleted del
END

IF UPDATE(fieldB)

BEGIN
INSERT INTO logtable
(field1,
Field2,
SELECT del.oldvalue,
(SELECT ins.newvalue
FROM inserted ins)
FROM deleted del
END

IF UPDATE(fieldC)

BEGIN
INSERT INTO logtable
(field1,
Field2,
SELECT del.oldvalue,
(SELECT ins.newvalue
FROM inserted ins)
FROM deleted del
END

Thanks in advance.

Jose.

 

[SQLDenis]

One of your fields in the logtable isn't big enough to store all the data from the field original table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Jomercat]

Got it!

Thanks so much!

Jose.

 

[SQLDenis]

np

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/