I need to insert dynamically the field names and values old and new in a trigger, in firebird 2.5

Question

I can insert table column names dynamically the problem is when I want to insert the new or old values in my log table I am getting a string 'old.Colname' or 'new.Colname' instead of old or new value.

DECLARE C_USER VARCHAR(255);
DECLARE VARIABLE OPERATION_EVENT CHAR(8);
DECLARE GROUPIDNO INT;
DECLARE LOGDATAID_NO INT;
DECLARE VARIABLE FN CHAR(31);
DECLARE VARIABLE NEWCOL CHAR(31);
DECLARE VARIABLE OLDCOL CHAR(31);

BEGIN

  SELECT CURRENT_USER FROM RDB$DATABASE INTO :C_USER;

  IF (DELETING) THEN
  BEGIN
    OPERATION_EVENT = 'DELETE';
  END
  ELSE
  BEGIN
    IF (UPDATING) THEN
      OPERATION_EVENT = 'UPDATE';
    ELSE
      OPERATION_EVENT = 'INSERT';
  END

  SELECT MAX(GROUPID) FROM LOG_DATA INTO :GROUPIDNO;

  IF(GROUPIDNO IS NULL) THEN    
    GROUPIDNO = 1;
  ELSE
    GROUPIDNO = GROUPIDNO + 1;

  IF(INSERTING) THEN
  BEGIN
FOR SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'ARAC' INTO :FN       DO
  BEGIN
        
        OLDCOL = 'old.'||:FN;
        NEWCOL = 'new.'||:FN;

        INSERT INTO LOG_DATA (OLD_VALUE,NEW_VALUE, COLUMN_NAME, TABLE_NAME, OPERATION, 
         CREATEDAT,USERS,GROUPID,LOGDATAID)                                      
        VALUES     (:OLDCOL,:NEWCOL,:FN,'ARAC',trim(:OPERATION_EVENT),
           current_timestamp,:C_USER,:GROUPIDNO,:LOGDATAID_NO + 1);
  END
END

Here is a screen shot of my log table, I want to insert the old and new values, but column names are being inserted as strings instead

Answer 1

The problem is that you are trying to reference the old and new context as strings, and that is not possible. The specific problem is:

OLDCOL = 'old.'||:FN;
NEWCOL = 'new.'||:FN;

This produces a string with value 'old.<whatever the value of FN is>' (and same for new). It does not produce the value of the column with the name in FN from the OLD or NEW contexts.

Unfortunately, it is not possible to dynamically reference the columns in the OLD and NEW contexts by name. You will explicitly need to use OLD.columnname and NEW.columnname in your code, which means that you will need to write (or generate) a trigger that inserts each column individually.

Alternatively, you could upgrade to Firebird 3, and use a UDR to define a trigger in native code, C# or Java (or other supported languages). These UDR engines allow you to dynamically reference the columns in the old and new context.

As an example, using the FB/Java external engine (check the readme in the repository on how to install FB/Java):

Create a CHANGELOG table:

create table changelog (
  id bigint generated by default as identity constraint pk_changelog primary key,
  tablename varchar(31) character set unicode_fss not null,
  row_id varchar(30) character set utf8,
  columnname varchar(31) character set unicode_fss not null,
  new_value varchar(2000) character set utf8,
  old_value varchar(2000) character set utf8,
  operation char(6) character set ascii not null,
  modification_datetime timestamp default localtimestamp not null 
)

And a FB/Java trigger:

package nl.lawinegevaar.fbjava.experiment;

import org.firebirdsql.fbjava.TriggerContext;
import org.firebirdsql.fbjava.Values;
import org.firebirdsql.fbjava.ValuesMetadata;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.EnumSet;
import java.util.Objects;
import java.util.Set;
import java.util.function.BiPredicate;

import static java.util.Collections.unmodifiableSet;
import static org.firebirdsql.fbjava.TriggerContext.Action.*;

public class ChangelogTrigger {

    private static final Set<TriggerContext.Action> SUPPORTED_ACTIONS =
            unmodifiableSet(EnumSet.of(INSERT, UPDATE, DELETE));

    private static final int ROW_ID_LENGTH = 30;
    private static final int VALUE_LENGTH = 2000;

    private static final String INSERT_CHANGELOG =
            "insert into changelog (tablename, row_id, columnname, new_value, old_value, operation) " 
                    + "values (?, ?, ?, ?, ?, ?)";

    public static void logChanges() throws SQLException {
        TriggerContext ctx = TriggerContext.get();
        TriggerContext.Action action = ctx.getAction();
        if (!SUPPORTED_ACTIONS.contains(action)) {
            return;
        }
        String tableName = ctx.getTableName();
        if (tableName.equals("CHANGELOG")) {
            throw new IllegalStateException("Cannot apply ChangelogTrigger to table " + tableName);
        }
        String identifierColumn = ctx.getNameInfo();
        ValuesMetadata fieldsMetadata = ctx.getFieldsMetadata();
        int identifierIdx = identifierColumn != null ? fieldsMetadata.getIndex(identifierColumn) : -1;
        Values oldValues = ctx.getOldValues();
        Values newValues = ctx.getNewValues();
        Values primaryValueSet = action == INSERT ? newValues : oldValues;
        String identifierValue = identifierIdx != -1
                ? truncate(asString(primaryValueSet.getObject(identifierIdx)), ROW_ID_LENGTH)
                : null;

        try (PreparedStatement pstmt = ctx.getConnection().prepareStatement(INSERT_CHANGELOG)) {
            pstmt.setString(1, tableName);
            pstmt.setString(2, identifierValue);

            BiPredicate<Object, Object> logColumn = action == UPDATE
                    ? ChangelogTrigger::acceptIfModified
                    : ChangelogTrigger::acceptAlways;

            boolean batchUsed = false;
            for (int idx = 1; idx <= fieldsMetadata.getParameterCount(); idx++) {
                Object oldValue = oldValues != null ? oldValues.getObject(idx) : null;
                Object newValue = newValues != null ? newValues.getObject(idx) : null;
                if (logColumn.test(oldValue, newValue)) {
                    String columnName = fieldsMetadata.getName(idx);
                    pstmt.setString(3, columnName);
                    pstmt.setString(4, truncate(asString(newValue), VALUE_LENGTH));
                    pstmt.setString(5, truncate(asString(oldValue), VALUE_LENGTH));
                    pstmt.setString(6, action.name());

                    pstmt.addBatch();
                    batchUsed = true;
                }
            }

            if (batchUsed) {
                pstmt.executeBatch();
            }
        }
    }

    private static boolean acceptAlways(Object oldValue, Object newValue) {
        return true;
    }

    private static boolean acceptIfModified(Object oldValue, Object newValue) {
        return !Objects.equals(oldValue, newValue);
    }

    private static String asString(Object value) {
        return value != null ? String.valueOf(value) : null;
    }

    private static String truncate(String value, int maxLength) {
        if (value == null || value.length() <= maxLength) {
            return value;
        }

        return value.substring(0, maxLength - 3) + "...";
    }
}

This FB/Java trigger is very generic and can be used for multiple tables. I haven't tested this trigger with all datatypes. For example, to be able to make the trigger work correctly with columns of type blob or other binary types will require additional work.

Build the trigger and load it into the database using the fbjava-deployer utility of FB/Java.

Then define the trigger on the table you want (in this case, I defined it on the TEST_CHANGELOG table):

create trigger log_test_changelog
  before insert or update or delete
  on test_changelog
  external name 'nl.lawinegevaar.fbjava.experiment.ChangelogTrigger.logChanges()!ID' 
  engine JAVA

The external name defines the routine to call (nl.lawinegevaar.fbjava.experiment.ChangelogTrigger.logChanges()) and the name of the (single) primary key column of the table (ID), which is used to log the identifier in the ROW_ID column.