Hi

I'm trying to create a trigger that can easily be applied to
current/new tables without major modification. This trigger will
create a item in another table called History for each field in that
row that has changed.

I think (although I could be very wrong) that I've pretty much got it
figure out, except comparing the old and new .fieldnames to see if
they have changed.

Preferable I would like to loop through all the fields in Old and
check them against New. Since I can't seem to find a way to do this,
I've been trying to use Execute Statement and looping through
RDB$Relation_Fields to compare each field. However it appears
Execute Statement runs outside of the current context because when I
get to the line

Execute Statement 'Old.' || FieldName INTO :OldString;

I get an error of Unknown Token 'Old'.

Problems with the trigger that I'm yet to face are below...
- Currently I've only writing it for updating so I can get
everything working, I will then expand it for Inserting and Deleting
(using the Firebird inserting, updating, deleting booleans)
- The comparing will most likely fail on Blob fields. This is why
I was originally wanting to loop through all the columns in Old.

I'm using Firebird 1.5. If anyone has some advice on problems with
the code or another way to do it, that would be great as I'm fairly
new to writting database triggers.


SET TERM ^^ ;
CREATE TRIGGER AA_HIST_CLIENTS FOR CLIENTS ACTIVE AFTER UPDATE
POSITION 0 AS
Declare Variable TableName VarChar(40);
Declare Variable RowIDName VarChar(20);
Declare Variable TableID Integer;
Declare Variable FieldName VarChar(40);
Declare Variable OldString VarChar(200);
Declare Variable NewString VarChar(200);
Declare Variable HistoryID Integer;
Declare Variable RowID Integer;
Declare Variable StaffID Integer;
BEGIN
TableName = 'Clients';
RowIDName = 'ClientID';
FOR SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = Upper(:TableName)
INTO :FieldName
DO BEGIN
Execute Statement 'Old.' || FieldName INTO :OldString;
Execute Statement 'New.' || FieldName INTO :NewString;
IF (OldString <> NewString) THEN BEGIN
HistoryID = Gen_ID(HistoryIDGen, 1);
SELECT RDB$RELATION_ID FROM RDB$RELATIONS WHERE
RDB$RELATION_NAME = Upper(:TableName) INTO :TableID;
EXECUTE STATEMENT 'New.' || RowIDName INTO :RowID;
Select StaffID from Users where DBHandle = CURRENT_CONNECTION
INTO :StaffID;

INSERT INTO HISTORY (HistoryID, StaffID, TableID, FieldName,
OldValue, NewValue, ChangeType, DateChanged, RowID)
VALUES
(:HistoryID, :StaffID, :TableID, :FieldName, :OldString, :NewString,
1, CURRENT_TIMESTAMP, :RowID);
END
END
END
^^
SET TERM ; ^^