Re: Triggers
From: The Magnet <art_at_unsu.com>
Date: Sun, 8 Aug 2010 17:18:21 -0700 (PDT)
Message-ID: <8249a530-4c12-471a-a86f-03fe5b4ec5ef_at_z28g2000yqh.googlegroups.com>
On Aug 8, 5:30 am, F van Nimwegen <fred.van.nimwe..._at_gmail.com> wrote:
> On Aug 8, 12:57 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On Aug 7, 5:30 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > 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)
>
> > 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?
>
> The actual value is the :new value.
>
> With the on delete trigger you can only use :old
>
> 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
Date: Sun, 8 Aug 2010 17:18:21 -0700 (PDT)
Message-ID: <8249a530-4c12-471a-a86f-03fe5b4ec5ef_at_z28g2000yqh.googlegroups.com>
On Aug 8, 5:30 am, F van Nimwegen <fred.van.nimwe..._at_gmail.com> wrote:
> On Aug 8, 12:57 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On Aug 7, 5:30 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > 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)
>
> > 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?
>
> The actual value is the :new value.
>
> With the on delete trigger you can only use :old
>
> 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
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? Received on Sun Aug 08 2010 - 19:18:21 CDT