Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!z28g2000yqh.googlegroups.com!not-for-mail From: The Magnet Newsgroups: comp.databases.oracle.misc Subject: Re: Triggers Date: Sun, 8 Aug 2010 17:18:21 -0700 (PDT) Organization: http://groups.google.com Lines: 60 Message-ID: <8249a530-4c12-471a-a86f-03fe5b4ec5ef@z28g2000yqh.googlegroups.com> References: <103df164-5527-41e9-be0d-3cddc28284a4@w30g2000yqw.googlegroups.com> NNTP-Posting-Host: 208.65.116.5 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1281313101 24337 127.0.0.1 (9 Aug 2010 00:18:21 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 9 Aug 2010 00:18:21 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: z28g2000yqh.googlegroups.com; posting-host=208.65.116.5; posting-account=r4PU2QoAAAD4sWAsISTEADGSF0Kki3_S User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/533.4 (KHTML, like Gecko) Chrome/5.0.375.125 Safari/533.4,gzip(gfe) Xref: news.cambrium.nl On Aug 8, 5:30=A0am, F van Nimwegen wrote: > On Aug 8, 12:57=A0am, The Magnet wrote: > > > > > > > On Aug 7, 5:30=A0pm, The Magnet wrote: > > > > Is there really any good way to track the changed columns in a trigge= r > > > 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. =A0Putting 50 I= F > > > statements is lame. =A0Is 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. =A0 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 =3D 'ABC'; > > Then, UPDATE history SET a=3D:NEW.a, b=3D????, =A0c=3D???? > > > Now, columns B & C, are they :NEW or :OLD? =A0Do 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?