1

I'm researching how to create a UDF to replicate a complete record of a Firebird table using triggers.

I want to create a revision/history about some tables, replicating the data to a mongodb database using a UDF to connect to the mongodb and insert the record there.

My question is: is possible to create a UDF and when I call it recognize the current table and row to get the columns values automaticly without passing the values as an argument or simply passing the OLD and NEW context variables?

Something like this:

CREATE TRIGGER 
    MY_REPLICATOR 
ACTIVE AFTER INSERT OR UPDATE OR DELETE 
POSITION 99 
ON MY_TABLE
AS
BEGIN
    /*INSIDE THE UDF IT IDENTIFIES THE CURRENT TABLE-ROW 
      AND REPLICATE ALL COLUMNS VALUES TO MY MONGODB*/
    MY_UDF_REPLICATION(OLD, NEW); 
END
Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
Beto Neto
  • 3,267
  • 3
  • 38
  • 70

1 Answers1

3

In Firebird 3 you can write external triggers on any decent language and get current record context. If you are still with FB 2.5 or older then use approach of calling UDF for every column in the table. Creation of such triggers according to a structure of a database could be easily automated.

CREATE TRIGGER MY_REPLICATOR FOR My_TABLE
  ACTIVE 
  AFTER INSERT OR UPDATE OR DELETE 
  POSITION 99 
AS
BEGIN
  MY_UDF_START_REPLICATE_ROW(CURRENT_TRANSACTION, 'MY_TABLE');

  MY_UDF_REPLICATE_INT_COLUMN(CURRENT_TRANSACTION, 'COLUMN_NAME_1', 
    OLD.column_name_1, NEW.column_name_1);
  MY_UDF_REPLICATE_VARCHAR_COLUMN(CURRENT_TRANSACTION, 'COLUMN_NAME_2', 
    OLD.column_name_2, NEW.column_name_2);
  ...
  -- call appropriate function for every column of the table
  ...

  MY_UDF_END_REPLICATE_ROW(CURRENT_TRANSACTION, 'MY_TABLE');
END
Andrej Kirejeŭ
  • 4,966
  • 2
  • 24
  • 30
  • Ok this is an approach, but there is no any other way to do this in FB 2.x ? I need to pass each column ? – Beto Neto May 27 '17 at 19:12
  • 1
    @BetoNeto Firebird 3 UDRs are the only real solution, for Firebird 2.x you might be able to do something by inserting into external tables. – Mark Rotteveel May 28 '17 at 09:21