Thomas Steinmaurer provides an inexpensive tool called IBLogmanager which
will create all triggers and logging tables automatically for you. Takes you
a few minutes no matter how complex your DB-Design is. Works quite good and
has a nice UI.

Rather spend the few bucks before getting headaches about the problem :-)

CU,
Nick Josipovic

BIT Institute
Prof. Dr. Franz Steffens
University of Mannheim
Am Victoriaturm 2
68163 Mannheim
Germany

Tel: ++49 621 12 86 30 25
Mob: ++49 179 133 44 16
Sec: ++49 621 12 86 30 10
Fax: ++49 621 12 86 30 09

nick.josipovic@...

> -----Original Message-----
> From: sugi [mailto:slim@...]
> Sent: Saturday, May 11, 2002 12:26 PM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Audit Trail/Log implementation (or How to loop
> through fields)
>
>
> Dear all,
>
> Is there a way to loop through all the fields of a table in IB/FB?
>
> In a "BEFORE DELETE" trigger, I need to backup all the fields to
> another table, something like this:
> ...
> for all_the_fields_in_this_table do begin
> copy_value_somewhere_else;
> end;
> ...
> The "BEFORE INSERT" trigger I'd need is a bit more complicated:
> ...
> for all_the_fields_in_this_table do begin
> if new_field_value <> old_field_value then begin
> copy_value_somewhere_else;
> end;
> end;
> ...
> Creating one of these triggers for one table is not a problem,
> but I have 20+ of these tables to track, so I cannot just make
> a duplicate triggers/tables for each of these.
>
> Currently my 'backup table structure' looks like this:
> ...
> Create table BAK_MASTER {
> id integer not null,
> operation varchar(32) not null,
> tablename varchar(32) not null,
> primary key pk_bak_master ( id )
> };
> Create table BAK_CLIENT {
> id integer not null, /*this is the fk from bak_master*/
> fieldname varchar(32) not null,
> fieldvalue varchar(32) not null,
> primary key pk_bak_detail ( id, fieldname )
> );
> ...
>
> I got as far as getting the field names with the following sp:
> ...
> CREATE PROCEDURE spGetFieldName(iTABLENAME VARCHAR(50))
> RETURNS ( FIELDNAME VARCHAR(50))
> AS
> begin
> /* Procedure Text */
> for select f.RDB$Field_name
> from RDB$relations r, RDB$relation_fields f
> where (r.RDB$relation_name = f.RDB$relation_Name) and
> (r.RDB$relation_name = Upper(:iTableName))
> into :FieldName
> do begin
> suspend;
> end;
> end!!
> ...
>
> But I'm stumped on how to proceed from here.
> Am I on the right track?
> Thanks in advance.
>
>
> Salam,
> sugi.