ASE Database forum - Getting Updated Columns in a Update Trigger

Author

Getting Updated Columns in a Update Trigger

Carlos Guilherme Blos

2005-04-05, 8:06 pm

Hi all,

I'm coding a Update trigger and I need to know which columns were
updated by the user.
I have the update (<column> ) function that return TRUE if the informed
column is updated, but I want to get this column to make a join.
For example:
Instead use the below code:

if update (dt_record)
begin
select dt_record from TABLE
end

I want to use a code that make possible get the column from a 'changing'
index and get the actual value for this column.

I don't know if this feature exist....

Thanks,
Guilherme

Carlos Guilherme Blos

2005-04-05, 8:06 pm

I Forgot to say that I am using ASE 12.5...
Thanks and sorry for the lack of information.

Carlos Guilherme Blos wrote:
> Hi all,
>
> I'm coding a Update trigger and I need to know which columns were
> updated by the user.
> I have the update (<column> ) function that return TRUE if the informed
> column is updated, but I want to get this column to make a join.
> For example:
> Instead use the below code:
>
> if update (dt_record)
> begin
> select dt_record from TABLE
> end
>
> I want to use a code that make possible get the column from a 'changing'
> index and get the actual value for this column.
>
> I don't know if this feature exist....
>
> Thanks,
> Guilherme

Rob Verschoor

2005-04-05, 8:06 pm

This is difficult: you could use execute-immediate to construct a join
dynamically and then execute it, but unfortunately you will not be able to
access the inserted/deleted tables from within exec-immediate. You could
work around this by copying the rows from inserted/deleted into another
table first and run the constructed join on that table, but that has
problems of its own (like the problem of creating a table in a transaction,
which is implied when you're in a trigger).
Why do you want to do this?

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"Carlos Guilherme Blos" <carlosgblos@gmail.com> wrote in message
news:4252c663@forums
-2-dub...[color=darkred]
> I Forgot to say that I am using ASE 12.5...
> Thanks and sorry for the lack of information.
>
>
> Carlos Guilherme Blos wrote:

2005-04-06, 8:03 pm

"if update (colname)" will indicate only that colname was
listed in the set list.
It resolves true even if the same value(s) was reapplied to
the row(s).

Ex. update authors set au_fname = 'Anne' where au_fname =
'Anne'

Here is an example of a trigger to extract any changes to
the aunmind index on the table authors:

create trigger authors_upd
on authors
for update as
select d.au_fname + ' ' + d.au_lname as OrgName,
i.au_fname + ' ' + i.au_lname as NewName
from deleted d, inserted i
where d.au_id = i.au_id -- unique row
identifier and
and (d.au_fname != i.au_fname -- columns of
interest connected by
or d.au_lname != i.au_lname) -- 'or' to get any
combined change

> Hi all,
>
> I'm coding a Update trigger and I need to know which
> columns were updated by the user.
> I have the update (<column> ) function that return TRUE if
> the informed column is updated, but I want to get this
> column to make a join. For example:
> Instead use the below code:
>
> if update (dt_record)
> begin
> select dt_record from TABLE
> end
>
> I want to use a code that make possible get the column
> from a 'changing' index and get the actual value for this
> column.
>
> I don't know if this feature exist....
>
> Thanks,
> Guilherme

Sponsored Links