We are trying to implement an audit trail of changed column values in a
table. I would really like to use a cursor to dynamically loop thru the
column list and log the changes. Something like:



FOR column_loop AS NO SCROLL INSENSITIVE CURSOR FOR
SELECT column_name from system views for this table

FOR READ ONLY
DO
IF old.column_name <> new.column_name THEN

INSERT INTO my audit_trail_table...

END IF;

END FOR;



I have not made it past:

a) can only reference column names hard coded in trigger (IF old.name <>
new.name...)

and

b) constructing sql for EXECUTE IMMEDIATE can not reference "old" and "new"
(sql_statement = 'IF old.name <> new.name..." followed by EXECUTE IMMEDIATE
sql_statement



does anyone have any ideas better than hard coding a long list of IF
statements for each column?



Thanks,

Bill

FWIW you don't actually need to compare new.column
to old.column. The if updating( 'column' ) trigger condition
which may also permit the use of a variable.

http://dcx.sybase.com/1101en/dbrefer...llanguage.html

"Bill Aumen" <bill (AT) aumenconsuling (DOT) com> wrote

Thanks Nick. I did see that construct, but if I understand it correctly I
still have to create a trigger with a clause for each column, rather than
using the system tables to dynamically loop thru each column at run-time. My
objective for the dynamic method is of course no maintenance to the trigger
when columns are added to the table.

My audit trail table would have one row for each column that was changed in
a single update. so I have a trail of individual column changed values.



"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4abbf169 (AT) forums-1-dub (DOT) ..

Sorry,

You seem to be correct the string thing is just a
syntactical convenience and not a real parameterizable
(if that is a word) value.

It seem you are back to adding triggers on every
column update conditions ...

FWIW The overhead of your approach seems excessive;
especially for triggers that fire frequently and for tables that
have a large number of columns (even if we assume there is
a way for you to do this). It seems to make a lot more sense
to just detect that a change took place (determined soledly
by trigger type) and determing which table, by whom and
when. If one really needs to know what changed they could
just translate the log for this operation. The complexity
of that search could simply require time and user and
object information that were gathered live. Dbtran -it and -u
switches could carry the rest of the day.


"Bill Aumen" <bill (AT) aumenconsulting (DOT) com> wrote