Обсуждение: 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

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