Thread: Triggers and Function.
How can I identify if a column is actually part of the UPDATE that fired the trigger. For example If I have a table with three columns col1, col2 and col3. The types don't matter to the question but may to the answer. I run "UPDATE example set col1 = 'NewVal', col2 = 2 where col1 = 'OldVal';" A BEFORE TRIGGER can test OLD.* against NEW.* and see col1 has changed col2 may or may not, OK to track what has actually changed, but how can I find out that col3 was not part of the update? I need to know if the client has updated a column, even if it still has the same value. Nottingham Clinical Research Limited Isaac Newton Centre, Nottingham Science and Technology Park, Nottingham NG7 2RH England Registered in England No. 2244384
On Aug 18, 2008, at 6:01 AM, Stephen Greensmith wrote: > How can I identify if a column is actually part of the UPDATE that > fired > the trigger. > > For example > > If I have a table with three columns col1, col2 and col3. The types > don't matter to the question but may to the answer. > > I run "UPDATE example set col1 = 'NewVal', col2 = 2 where col1 = > 'OldVal';" > > A BEFORE TRIGGER can test OLD.* against NEW.* and see col1 has changed > col2 may or may not, OK to track what has actually changed, but how > can > I find out that col3 was not part of the update? > > I need to know if the client has updated a column, even if it still > has > the same value. I'm pretty sure this isn't possible. Why do you need it? -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Attachment
Adding -novice back in. On Tue, Aug 26, 2008 at 08:34:45AM +0100, Stephen Greensmith wrote: > Thanks its looking that way. > > Why because I need to know if the application is setting a value in a > column or not. A bit like having a default on an update as well as an > insert. Ok, but why do you need to know if the field appears in the UPDATE, even if it's value doesn't change? > On Fri, 2008-08-22 at 06:38, Decibel! wrote: > > On Aug 18, 2008, at 6:01 AM, Stephen Greensmith wrote: > > > How can I identify if a column is actually part of the UPDATE that > > > fired > > > the trigger. > > > > > > For example > > > > > > If I have a table with three columns col1, col2 and col3. The types > > > don't matter to the question but may to the answer. > > > > > > I run "UPDATE example set col1 = 'NewVal', col2 = 2 where col1 = > > > 'OldVal';" > > > > > > A BEFORE TRIGGER can test OLD.* against NEW.* and see col1 has changed > > > col2 may or may not, OK to track what has actually changed, but how > > > can > > > I find out that col3 was not part of the update? > > > > > > I need to know if the client has updated a column, even if it still > > > has > > > the same value. > > > > > > I'm pretty sure this isn't possible. Why do you need it? -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
--- On Wed, 8/27/08, Decibel! <decibel@decibel.org> wrote: > > Why because I need to know if the application is > setting a value in a > > column or not. A bit like having a default on an > update as well as an > > insert. > > Ok, but why do you need to know if the field appears in the > UPDATE, even > if it's value doesn't change? > Can't the changes be inserted into an audit table as well as updated in the target table? Bruce