Re: Triggers
Date: Fri, 13 Aug 2010 11:32:09 +0200
Message-ID: <8ckhopF833U1_at_mid.individual.net>
On 08/09/2010 02:18 AM, The Magnet wrote:
> 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.
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.
> 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?
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/B19306_01/server.102/b14200/statements_7004.htm#sthref7990
> 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?
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/Received on Fri Aug 13 2010 - 04:32:09 CDT