Hi Christian,

Thank you very much, this is excellent and very clear!

I will try it immediately on a single table, then look at how to adapt things for each table in the database.

Kind regards Michael.

--- In firebird-support@yahoogroups.com, "chris.waldmann" <Christian.Waldmann@...> wrote:
>
>
>
>
>
>
>
>
>
> >
> > Your implementation sounds very interesting. Could you post more details as to how you actually did it?
> >
> > Thanks, Michael
> >
> > --- In firebird-support@yahoogroups.com, "chris.waldmann"
>
> Hi Michael
>
> I have added some SQL example code to illustrate my system for audit trail:
>
> I have implemented an audit trail log with before insert and before update triggers that store the new version also in the audit trail table. The actual version is stored twice, what is very handy to have fast access to the actual view and easy access to all data in the audit trail tables to find changes.
>
> CREATE TABLE TBLSTUDY (
> UNIQUEID BIGINT NOT NULL,
> LABEL VARCHAR(20) NOT NULL,
> TESTITEM VARCHAR(50),
> STARTDATE TIMESTAMP,
> APPLDATE TIMESTAMP,
> ENDDATE TIMESTAMP,
> STATE CHAR(1),
> FOODPERDAY FLOAT NOT NULL,
> FOODMINPERDAY FLOAT NOT NULL,
> WHY VARCHAR(128)
> );
>
> CREATE TABLE SHDWTBLSTUDY (
> UNIQUEID BIGINT NOT NULL,
> UPDATEWHEN TIMESTAMP,
> UPDATEWHAT BIGINT NOT NULL,
> UPDATEWHO VARCHAR(32),
> UPDATEWHY VARCHAR(128),
> LABEL VARCHAR(20) NOT NULL,
> TESTITEM VARCHAR(50),
> STARTDATE TIMESTAMP,
> APPLDATE TIMESTAMP,
> ENDDATE TIMESTAMP,
> STATE CHAR(1),
> FOODPERDAY FLOAT NOT NULL,
> FOODMINPERDAY FLOAT NOT NULL
> );
>
> The trigger adds the w information (who, when, what, why) to the audit trail table. "what" is a foregin key to the unique key of the actual table record.
>
> /* Trigger: TRG_UPDATE_STUDY_SHADOW */
> CREATE OR ALTER TRIGGER TRG_UPDATE_STUDY_SHADOW FOR TBLSTUDY
> ACTIVE BEFORE UPDATE POSITION 0
> as
> begin
> insert into SHDWTBLSTUDY (UPDATEWHEN, UPDATEWHAT, UPDATEWHO, UPDATEWHY, LABEL, TESTITEM, STARTDATE, APPLDATE, ENDDATE, STATE, FOODPERDAY, FOODMINPERDAY)
> values ('NOW', new.UNIQUEID, current_user, new.WHY, new.LABEL, new.TESTITEM, new.STARTDATE, new.APPLDATE, new.ENDDATE, new.STATE, new.FOODPERDAY, new.FOODMINPERDAY);
> new.WHY = '';
> end
>
>
> To find the data at a given time, I have a descending index on the change timestamp field and "select first 1" "where timestamp <= reporttime" "order by timestamp descending".
> There is also a computed index on "cast(STS.UPDATEWHEN as date) descending" to get faster access (I think!?).
>
> create or alter procedure PROCGETALLSTUDYSETTINGS (
> REPORTDATE date,
> STUDY bigint)
> returns (
> UIDUPDATE bigint,
> LABEL varchar(20),
> TESTITEM varchar(50),
> STARTDATE timestamp,
> APPLDATE timestamp,
> ENDDATE timestamp,
> STATE char(1),
> FOODPERDAY float,
> FOODMINPERDAY float)
> as
> begin
> select first 1 STS.UNIQUEID, STS.LABEL, STS.TESTITEM, STS.STARTDATE, STS.APPLDATE,
> STS.ENDDATE, STS.STATE, STS.FOODPERDAY, STS.FOODMINPERDAY
> from SHDWTBLSTUDY STS
> where STS.UPDATEWHAT = :STUDY
> and cast(STS.UPDATEWHEN as date) <= :REPORTDATE
> order by STS.UPDATEWHEN descending
> into UIDUPDATE, LABEL, TESTITEM, STARTDATE, APPLDATE, ENDDATE, STATE, FOODPERDAY,
> FOODMINPERDAY;
> suspend;
> end
>
>
> To report the changes, I have a stored procedure that returns the
> difference of one row to the next older corresponding row.
>
> create or alter procedure PROCGETAUDITTRAILSTUDY (
> IN_UID bigint,
> IN_WHAT bigint)
> returns (
> OUT_LABEL varchar(64),
> OUT_OLD varchar(64),
> OUT_NEW varchar(64))
> as
> declare variable VAR_UID_OLD bigint;
> /* some more variable skipped */
> begin
> /* select the new field contents */
> select STS.LABEL, STS.TESTITEM, STS.STARTDATE, STS.APPLDATE, STS.ENDDATE, STS.STATE, STS.FOODPERDAY,
> STS.FOODMINPERDAY
> from SHDWTBLSTUDY STS
> where STS.UNIQUEID = :IN_UID
> into VAR_LABEL_NEW, VAR_TESTITEM_NEW, VAR_STARTDATE_NEW, VAR_APPLDATE_NEW,
> VAR_ENDDATE_NEW, VAR_STATE_NEW, VAR_FOODPERDAY_NEW, VAR_FOODMINPERDAY_NEW;
>
> /* select the old field contents */
> select first 1 STS.UNIQUEID, STS.LABEL, STS.TESTITEM, STS.STARTDATE, STS.ENDDATE, STS.STATE,
> STS.FOODPERDAY, STS.FOODMINPERDAY
> from SHDWTBLSTUDY STS
> where STS.UPDATEWHAT = :IN_WHAT
> and STS.UNIQUEID < :IN_UID
> order by STS.UNIQUEID descending
> into VAR_UID_OLD, VAR_LABEL_OLD, VAR_TESTITEM_OLD, VAR_STARTDATE_OLD, VAR_ENDDATE_OLD, VAR_STATE_OLD,
> VAR_FOODPERDAY_OLD, VAR_FOODMINPERDAY_OLD;
>
> /* return the changes of first field*/
> if (VAR_LABEL_OLD is distinct from VAR_LABEL_NEW) then begin
> OUT_LABEL = 'Study Number';
> OUT_OLD = VAR_LABEL_OLD;
> OUT_NEW = VAR_LABEL_NEW;
> suspend;
> end
> /* return the changes of second field*/
> if (VAR__OLD is distinct from VAR__NEW) then begin
> OUT_LABEL = '';
> OUT_OLD = VAR__OLD;
> OUT_NEW = VAR__NEW;
> suspend;
> end
> /* and so on ....*/
> end
>
> The difference is returned as strings to be listed as report.
>
> Hope the SQL and the comments help to adapt my concept.
>
> Good luck
> Christian
>