Thread: Triggers and Function.

Triggers and Function.

From
Stephen Greensmith
Date:
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

Re: Triggers and Function.

From
Decibel!
Date:
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

Re: Triggers and Function.

From
Decibel!
Date:
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

Re: Triggers and Function.

From
Bruce Hyatt
Date:
--- 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