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 (currently only on updating) will create a item in another table called History for each field in that row that has changed.

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

Any help as to how I can achieve this goal of writing a trigger that isn't tied to the fields of a table (and wouldn't need to be updated each time a table is altered would be greatly appreciated.
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 ;  ^^

Open in new window

beezeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
which version of firebird are you using?

you say currently only on updateing, firebird 2+ will not allow you to refer to old. in an insert trigger or new. in a delete trigger
0
beezeeAuthor Commented:
I'm using Firebird 1.5  When I expand it to do insert and deletes, I'll use the inserting, updating and deleting booleans to ensure i'm only calling Old. and New. where I should be.
0
Nick UpsonPrincipal Operations EngineerCommented:
you code doesn't make sense, "Execute Statement 'Old.' || FieldName INTO :OldString;" - what is this supposed to do, it can't execute 'Old.' || FieldName INTO :OldString; as that is not valid

I think you need seperate statements

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

becomes:

OldString = 'Old.' || FieldName ' = Oldvaluestring'; /* construct the statement */
Execute Statement OldString /* get value into OLDvaluestring */
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

beezeeAuthor Commented:
I'd tried as you suggested (I added another || between Fieldname and ' = Oldvaluestring').  I get the same error unknown token Old on the Execute Statement line.  I have a feeling it isn't going to be possible as the statement is being executed outside the context of the trigger.  However if you have any ideas, I'm more than happy to give them a go.

With your code won't the statement try to change Old.Fieldname's value because it is on the left side?  The book I've been using as a reference for Firebird code shows that you should be able to use Execute Statement to execute a string and then use Into to assign it to a variable, much like Select in PSQL.
0
Nick UpsonPrincipal Operations EngineerCommented:
adding this from the mailing list in case anyone else finds this question

> Using EXECUTE STATEMENT with a context variable (NEW or OLD)
> will never work, cause that's only available inside a trigger, not in a
> new statement
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
beezeeAuthor Commented:
Thanks for the help, I thought that the it would be a context issue, oh well, back to the drawing board...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.