Is there really any good way to track the changed columns in a trigger
on an update?

Say I have a table of 100 columns and I am doing some history
recording into another table for 50 of those columns. Putting 50 IF
statements is lame. Is there a better way to do it?

Maybe something similar to
DECODE(email,:NEW.email, :NEW.email, :OLD.email)

On Aug 7, 5:30*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
I was thinking and I guess I should be more explicit:

On an UPDATE trigger, which column values are :NEW and which
are :OLD. If I update column A and need the values for column B & C
for something else, do I reference them as :NEW or :OLD (this is a
BEFORE trigger).

So, UPDATE tablex SET a = 'ABC';
Then, UPDATE history SET a=:NEW.a, b=????, c=????

Now, columns B & C, are they :NEW or :OLD? Do I need to test each
column I need to get the actual value?

On Aug 8, 12:57*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
The actual value is the :new value.

With the on delete trigger you can only use ld

will you always write to history or only when one of the 50 columns
are changed?
When you always write history their is no need to test
when you only write history when one of the 50 columns are changed you
had to test

On Aug 8, 5:30*am, F van Nimwegen <fred.van.nimwe... (AT) gmail (DOT) com> wrote:

I will always be writing history. So, say I update column A, which I
will refer to as :NEW.A, how do I refer to the other columns? B, C,
D?

Basically then want a before image of the record in another table.
So, on an insert, the new record goes into the history table. On an
update the old values go into the history table. On delete, the old
values go into the table.

But for UPDATE, I'm not sure how I would access all the values in the
record. If the trigger is FOR EACH ROW, then anything I do is
affecting THAT row, yes?

So, I can say INTO INTO history (:OLD.a, :OLD.b, :OLD.c). Nothing
else, since it is referring to the current row? Is that correct?

On 08/09/2010 02:18 AM, The Magnet wrote:
That logic seems a bit inconsistent to me. For example, with that logic
you will store the inserted record two times in the history table (once
on insertion and at the first UPDATE). I would do it differently: the
master table contains the *current* record (if it is not there it has
been deleted). The history table contains all past records. With that
logic you only need a trigger for UPDATE and DELETE. The trigger logic
will also become simpler: the UPDATE and DELETE triggers simply inserts
OLD values.

I am not sure what you mean by "THAT row". "FOR EACH ROW" means that
the trigger runs for all rows affected by the UPDATE, DELETE or INSERT.

http://download.oracle.com/docs/cd/B...htm#sthref7990

If you mean "current" == "row before the modification" then yes.

Btw, if you are unsure about these behaviors it's probably best to play
around with this in a test database. I always find that experience
gained that way tends to stick better in my memory than just reading
docs (which of course is mandatory :-)).

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/