Hi,
I don't think there is any way to take the shortcut you're trying to do with NEW and OLD. What you can do, is to write a stored procedure that selects the source code of a trigger!

Something like (I've never done this before, and do write more procedures in Delphi than Firebird, so do expect lots of errors):

Create procedure CreateTrigger(
MyTable CHAR(31))
Returns (
TriggerCode VARCHAR(30000))
As
DECLARE VARIABLE FIELDNAME CHAR(31);
BEGIN
MyTable = Upper(:MyTable);
TriggerCode' = 'CREATE TRIGGER AI_' || MyTable || ' FOR ' || MyTable || ' ACTIVE AFTER INSERT POSITION 0 AS';
TriggerCode = TriggerCode || ' Declare Variable RowIDName VarChar(20); ';
TriggerCode = TriggerCode || ' Declare Variable TableID Integer; ';
TriggerCode = TriggerCode || ' Declare Variable HistoryID Integer; ';
TriggerCode = TriggerCode || ' Declare Variable RowID Integer; ';
TriggerCode = TriggerCode || ' Declare Variable StaffID Integer; ';
TriggerCode = TriggerCode || ' BEGIN ';
FOR SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = MyTable
INTO :FIELDNAME
DO BEGIN
TriggerCode = TriggerCode || 'if (new.' || FieldName || ' <> old.' || FieldName || ') THEN BEGIN ';
TriggerCode = TriggerCode || ' Select StaffID from Users where DBHandle = CURRENT_CONNECTION INTO :StaffID; ';
TriggerCode = TriggerCode || ' SELECT RDB$RELATION_ID FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = Upper(:TableName) INTO :TableID; ';
TriggerCode = TriggerCode || ' HistoryID = Gen_ID(HistoryIDGen, 1); ';
TriggerCode = TriggerCode || 'INSERT INTO HISTORY (HistoryID, StaffID, TableID, FieldName, OldValue, NewValue, ChangeType, DateChanged, RowID) ';
TriggerCode = TriggerCode || 'VALUES (:HistoryID, :StaffID, :TableID, ';
TriggerCode = TriggerCode || FIELDNAME || ', old.'|| FIELDNAME || ', ', new.' || FIELDNAME || ', ' 1, CURRENT_TIMESTAMP, :RowID); ';
TriggerCode = TriggerCode || 'END ';
END
TriggerCode = TriggerCode || 'END; ';
Suspend;
END

Hence, when you do something like SELECT TriggerCode from CreateTrigger(CUSTOMER), what would be returned is something that you should execute to get your trigger. You can try using EXECUTE STATEMENT rather than SUSPEND and get it inserted directly, it is just the thought of having EXECUTE STATEMENT doing DDL that I'm not certain whether is safe or not.

And I didn't understand what is the point with RowID, so that bit won't work above.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of taoca
Sent: 7. februar 2008 03:20
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Creating a Database History

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



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links