>
> 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