Triggers
in [Oracle Help]
|
Prev: Oracle Import
Next: external table problems
From: The Magnet on 7 Aug 2010 18:30 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)
From: The Magnet on 7 Aug 2010 18:57 On Aug 7, 5:30 pm, The Magnet <a...(a)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?
From: F van Nimwegen on 8 Aug 2010 06:30 On Aug 8, 12:57 am, The Magnet <a...(a)unsu.com> wrote: > On Aug 7, 5:30 pm, The Magnet <a...(a)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
From: The Magnet on 8 Aug 2010 20:18 On Aug 8, 5:30 am, F van Nimwegen <fred.van.nimwe...(a)gmail.com> wrote: > On Aug 8, 12:57 am, The Magnet <a...(a)unsu.com> wrote: > > > > > > > On Aug 7, 5:30 pm, The Magnet <a...(a)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?
From: Robert Klemme on 13 Aug 2010 05:32 On 08/09/2010 02:18 AM, The Magnet wrote: > On Aug 8, 5:30 am, F van Nimwegen<fred.van.nimwe...(a)gmail.com> wrote: >> On Aug 8, 12:57 am, The Magnet<a...(a)unsu.com> wrote: >> >>> On Aug 7, 5:30 pm, The Magnet<a...(a)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/
|
Pages: 1 Prev: Oracle Import Next: external table problems |