Generic audit_log for FireBird

[Bhavbhuti Nathwani]

Hi all

Is there an equivalent feature in FireBird?

https://sqlservercarpenter.com/2018/05/18/generic-sql-trigger-for-audit-log/

The idea would be to record all user action of add, edit, or delete of user data.

Back in 2012, there was one dev version of FlameRobin that had this that would auto create the triggers once the tables were right-clicked and the correct option selected.

Unfortunately, I have lost that version of FlameRobin and my project sample, but somehow I have 3 sample SQL statements that I had created based on the structures that FlameRobin created.

Any advise would be helpful

Kind regards

Bhavbhuti

[Dimitry Sibiryakov]

Bhavbhuti Nathwani wrote 10.04.2023 15:30:
> Is there an equivalent feature in FireBird?

Yes and much simplier:

CREATE OR ALTER TRIGGER TR_Audit_Log_tablename
FOR tablename ACTIVE
AFTER INSERT OR UPDATE OR DELETE AS
DECLARE VARIABLE C_OP CHAR(1) = '?'
BEGIN
C_OP = CASE WHEN (INSERTING) THEN 'I' WHEN (UPDATING) THEN 'U' WHEN
(DELETING) THEN 'D' END;
INSERT INTO INSERT INTO AUDIT_LOG
(TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
VALUES
('tablename', 'keyname', :new.key,
:C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_HOST'), CURRENT_USER, LOCAL_TIMESTAMP,
'colname', :old.column, :new.column);
END

--
WBR, SD.

[Bhavbhuti eBooks]

Thank SD for your reply and suggestion.  I have added the table structure

CREATE TABLE UAUDIT_LOG
(
  TABLE_NAME VARCHAR(100) DEFAULT '',
  PRIMARY_KEY_COLUMN_NAME VARCHAR(100) DEFAULT '',
  PRIMARY_KEY_COLUMN_VALUE VARCHAR(100) DEFAULT '',
  AUDIT_ACTION CHAR(1) DEFAULT '',
  HOST_NAME VARCHAR(100) DEFAULT '',
  APP_NAME VARCHAR(100) DEFAULT '',
  AUDIT_USERID VARCHAR(100) DEFAULT '',
  AUDIT_DATETIME TIMESTAMP DEFAULT NULL,
  COLUMN_NAME VARCHAR(100) DEFAULT '',
  COLUMN_OLD_VALUE VARCHAR(2000) DEFAULT '',
  COLUMN_NEW_VALUE VARCHAR(2000) DEFAULT ''
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON UAUDIT_LOG TO  SYSDBA WITH GRANT OPTION;

but I was not able to successfully create the trigger

SET TERM ^ ;

CREATE OR ALTER TRIGGER TR_uAudit_Log_tSaleInvoice

ACTIVE
AFTER INSERT OR UPDATE OR DELETE

On tSaleInvoice

AS
DECLARE VARIABLE C_OP CHAR(1) = '?'
BEGIN
  C_OP = CASE WHEN (INSERTING) THEN 'I' WHEN (UPDATING) THEN 'U' WHEN (DELETING) THEN 'D' END;

  INSERT INTO uAUDIT_LOG

  (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
   AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
   COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
VALUES

  ('tSaleInvoice', 'iID', :new.iID,
   :C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_HOST'), 'C26', CURRENT_USER, LOCAL_TIMESTAMP,
   'iBuyerID', :old.iBuyerID, :new.iBuyerID);
END^

SET TERM ; ^

I get the following error:

Error: *** IBPP::SQLException ***
Context: Statement::Prepare(

CREATE OR ALTER TRIGGER TR_uAudit_Log_tSaleInvoice

ACTIVE
AFTER INSERT OR UPDATE OR DELETE

On tSaleInvoice

AS
DECLARE VARIABLE C_OP CHAR(1) = '?'
BEGIN
  C_OP = CASE WHEN (INSERTING) THEN 'I' WHEN (UPDATING) THEN 'U' WHEN (DELETING) THEN 'D' END;

  INSERT INTO uAUDIT_LOG

  (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
   AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
   COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
VALUES

  ('tSaleInvoice', 'iID', :new.iID,
   :C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_HOST'), 'C26', CURRENT_USER, LOCAL_TIMESTAMP,
   'iBuyerID', :old.iBuyerID, :new.iBuyerID);
END )
Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 9, column 1
BEGIN


Total execution time: 0.017s

I might have missed something, if so please advise.  Also, if it matters I am still using FB 2.5.

My understanding is that I will have to define this for each field in each table?  'iBuyerID', :old.iBuyerID, :new.iBuyerID);

Or is this generic code? and I had to leave 'colname', :old.column, :new.column); as it is?

Also, is it possible to have the audit table in a separate database altogether?

Kindly advise further

Thanks and regards

Bhavbhuti

[Mark Rotteveel]

On 11-04-2023 09:11, Bhavbhuti eBooks wrote:
> I get the following error:
>
> Error: *** IBPP::SQLException ***
> Context: Statement::Prepare(
>
> CREATE OR ALTER TRIGGER TR_uAudit_Log_tSaleInvoice
> ACTIVE
> AFTER INSERT OR UPDATE OR DELETE
> On tSaleInvoice
> AS
> DECLARE VARIABLE C_OP CHAR(1) = '?'
> BEGIN

[..]

> Message: isc_dsql_prepare failed
>
> SQL Message : -104
> Invalid token
>
> Engine Code    : 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 9, column 1
> BEGIN
>
>
> Total execution time: 0.017s
>
> I might have missed something, if so please advise.  Also, if it matters
> I am still using FB 2.5.

The DECLARE statement must be terminated by a semicolon (so `DECLARE
VARIABLE C_OP CHAR(1) = '?';`).

Mark
--
Mark Rotteveel

[Bhavbhuti eBooks]

Thanks Mark.  Yes, that was one, plus there were a few : before OLD and
NEW that needed to be removed, plus local_timestamp needed to be
replaced as well as the second parameter of get_context but yes I have a
sample working thanks to you and SD.

Now my question is, is this doable as a loop rather than naming each
field individually?  This would help when fields are added the trigger
would automatically adapt

Please advise

SET TERM ^ ;

CREATE OR ALTER TRIGGER TR_Audit_Log_tSaleInvoice for tSaleInvoice
ACTIVE
AFTER INSERT OR UPDATE OR DELETE position 0

AS
DECLARE VARIABLE C_OP CHAR(1) = '?';
BEGIN

  C_OP = CASE WHEN (INSERTING) THEN 'I' WHEN (UPDATING) THEN 'U' WHEN
(DELETING) THEN 'D' END;

  if (old.cBk <> new.cBk) then

      INSERT INTO uAUDIT_LOG
      (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
       AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
       COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
    VALUES
      ('tSaleInvoice', 'iID', new.iID,

       :C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS'), 'C26',
CURRENT_USER, CURRENT_TIMESTAMP,
       'cBk', old.cBk, new.cBk);

  if (old.iBuyerID <> new.iBuyerID) then

      INSERT INTO uAUDIT_LOG
      (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
       AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
       COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
    VALUES
      ('tSaleInvoice', 'iID', new.iID,

       :C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS'), 'C26',
CURRENT_USER, CURRENT_TIMESTAMP,

       'iBuyerID', old.iBuyerID, new.iBuyerID);
END

^

SET TERM ; ^

Kind regards

Bhavbhuti

[Dimitry Sibiryakov]

Bhavbhuti eBooks wrote 11.04.2023 12:42:
> Now my question is, is this doable as a loop rather than naming each field
> individually?

No.

--
WBR, SD.

[Bhavbhuti eBooks]

:(


I was testing delete using the Trigger I shared above.  The Delete
records are not created in the audit log.

Do we need to move the Delete logging into a Before trigger?

Kind regards

Bhavbhuti

[Dimitry Sibiryakov]

Bhavbhuti eBooks wrote 11.04.2023 13:27:
> I was testing delete using the Trigger I shared above.  The Delete records are
> not created in the audit log.
>
> Do we need to move the Delete logging into a Before trigger?

No. If the records are not created it means that something is wrong, but it
is not the trigger's phase.
"The trigger you shared above" has a bug in conditions regarding NULL handling.

--
WBR, SD.

[Bhavbhuti eBooks]

I had missed the handling of I and D flag where there was either new (I)
or old (D) values.  I have corrected my code.  Please let me know if I
need to handle null further.

SET TERM ^ ;

CREATE OR ALTER TRIGGER TR_Audit_Log_tSaleInvoice for tSaleInvoice
ACTIVE
AFTER INSERT OR UPDATE OR DELETE position 0
AS
DECLARE VARIABLE C_OP CHAR(1) = '?';

DECLARE VARIABLE I_ID ID;

BEGIN
  C_OP = CASE WHEN (INSERTING) THEN 'I' WHEN (UPDATING) THEN 'U' WHEN
(DELETING) THEN 'D' END;

  I_ID = CASE WHEN (INSERTING) THEN new.iID WHEN (UPDATING) THEN
old.iID WHEN (DELETING) THEN old.iID END;

  if (:C_OP = 'I' OR :C_OP = 'D' OR old.cBk <> new.cBk) then

      INSERT INTO uAUDIT_LOG
      (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
       AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
       COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
    VALUES

      ('tSaleInvoice', 'iID', :I_ID,

       :C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS'), 'C26',
CURRENT_USER, CURRENT_TIMESTAMP,
       'cBk', old.cBk, new.cBk);

  if (:C_OP = 'I' OR :C_OP = 'D' OR old.iBuyerID <> new.iBuyerID) then

      INSERT INTO uAUDIT_LOG
      (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
       AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
       COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
    VALUES

      ('tSaleInvoice', 'iID', :I_ID,

       :C_OP, RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS'), 'C26',
CURRENT_USER, CURRENT_TIMESTAMP,
       'iBuyerID', old.iBuyerID, new.iBuyerID);
END
^

SET TERM ; ^

This has the two fields logged in I, U, D status.  I can now expand this
proof of concept for the rest of the fields and tables.

Please advise if I am missing anything upfront

Thanks for your continued help

Kind regards

Bhavbhuti

[Karol Bieniaszewski]

>> old.cBk <> new.cBk

 

old.cBk DISTINCT FROM new.cBk

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/eeadbcce-17d1-acc1-f37c-cd8c068f58c7%40gmail.com.

 

[Bhavbhuti eBooks]

On 11-04-2023 05:25 pm, Karol Bieniaszewski wrote:

>> old.cBk <> new.cBk

 

old.cBk DISTINCT FROM new.cBk

 

Regards,

Karol Bieniaszewski

Thanks Karol but I guess this does not work in FB 2.5

For SD and Mark.  I just went overboard with the variables and made repetitive values right at the top

SET TERM ^ ;

CREATE OR ALTER TRIGGER TR_Audit_Log_tSaleInvoice for tSaleInvoice
ACTIVE
AFTER INSERT OR UPDATE OR DELETE position 0
AS
DECLARE VARIABLE C_OP CHAR(1) = '?';
DECLARE VARIABLE I_ID ID;

DECLARE VARIABLE C_Table_Name Varchar(100) = 'tSaleInvoice';
DECLARE VARIABLE C_Primary_Key_Column_Name VarCHAR(100) = 'iID';
DECLARE VARIABLE C_App_Name VarCHAR(100) = 'C26';
DECLARE VARIABLE C_Host_Name VarCHAR(100);
DECLARE VARIABLE C_Audit_UserID VarCHAR(100) = CURRENT_USER;
DECLARE VARIABLE T_Audit_DateTime TIMESTAMP = CURRENT_TIMESTAMP;

BEGIN
  C_OP = CASE WHEN (INSERTING) THEN 'I' WHEN (UPDATING) THEN 'U' WHEN (DELETING) THEN 'D' END;
  I_ID = CASE WHEN (INSERTING) THEN new.iID WHEN (UPDATING) THEN old.iID WHEN (DELETING) THEN old.iID END;

  C_Host_Name = RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS');

 
  if (:C_OP = 'I' OR :C_OP = 'D' OR old.cBk <> new.cBk) then
      INSERT INTO uAUDIT_LOG
      (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
       AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
       COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
    VALUES

      (:C_Table_Name, :C_Primary_Key_Column_Name, :I_ID,
       :C_OP, :C_Host_Name, :C_App_Name , :C_Audit_UserID, :T_Audit_DateTime,

       'cBk', old.cBk, new.cBk);
 
  if (:C_OP = 'I' OR :C_OP = 'D' OR old.iBuyerID <> new.iBuyerID) then
      INSERT INTO uAUDIT_LOG
      (TABLE_NAME, PRIMARY_KEY_COLUMN_NAME, PRIMARY_KEY_COLUMN_VALUE,
       AUDIT_ACTION, HOST_NAME, APP_NAME, AUDIT_USERID, AUDIT_DATETIME,
       COLUMN_NAME, COLUMN_OLD_VALUE, COLUMN_NEW_VALUE)
    VALUES

      (:C_Table_Name, :C_Primary_Key_Column_Name, :I_ID,
       :C_OP, :C_Host_Name, :C_App_Name , :C_Audit_UserID, :T_Audit_DateTime,

       'iBuyerID', old.iBuyerID, new.iBuyerID);
END
^

SET TERM ; ^

Thanks and regards

Bhavbhuti

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4Pwkmm455jzky3%40smtp.poczta.onet.pl.

[Omacht András]

old.cBk IS DISTINCT FROM new.cBk

Andras

-------- Eredeti üzenet --------

Feladó: Bhavbhuti eBooks <bhavbhu...@gmail.com>

Dátum: 2023. 04. 11. 14:15 (GMT+01:00)

Címzett: firebird...@googlegroups.com, livius...@poczta.onet.pl

Tárgy: Re: ODP: [firebird-support] Generic audit_log for FireBird

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d97e255a-d813-6be9-d723-d9fbe5edbd14%40gmail.com.

[Bhavbhuti eBooks]

On 11-04-2023 06:00 pm, Omacht András wrote:

old.cBk IS DISTINCT FROM new.cBk

Andras

Thanks Andras.  Implemented your and Karol's suggestion

Kind regards

Bhavbhuti

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3ce89898a8d14665a04a80d8ee9f5511%40mail3.mve.hu.

[Bhavbhuti eBooks]

Any possibility the audit_log table is in a different database and stored as such from within the trigger?

Kind regards

Bhavbhuti

[Dimitry Sibiryakov]

Bhavbhuti eBooks wrote 11.04.2023 14:45:
> Thanks Andras.  Implemented your and Karol's suggestion

You could do it yourself if you read documentation from
https://firebirdsql.org/en/firebird-rdbms/

--
WBR, SD.

[Dimitry Sibiryakov]

Bhavbhuti eBooks wrote 11.04.2023 14:47:
> Any possibility the audit_log table is in a different database and stored as
> such from within the trigger?

Yes: EXECUTE STATEMENT ON EXTERNAL DATA SOURCE. You can read about it in the
documentation I mentioned above.

--
WBR, SD.

[Bhavbhuti eBooks]

Thanks SD.  Sorry, I might have missed your previous mention of this on
this thread.  I have giving it a quick look, looks like I will have to
mailmerge the INSERT statements into a text string and then use the
EXTERNAL DATA SOURCE to send it to the other database.  Will consider
this option, thanks again for your continued help

Kind regards

Bhavbhuti

[Dimitry Sibiryakov]

Bhavbhuti eBooks wrote 11.04.2023 16:43:
> I have giving it a quick look, looks like I will have to mailmerge the INSERT
> statements into a text string and then use the EXTERNAL DATA SOURCE to send it
> to the other database.

No, it would be a big newbie mistake. You must use parameters.

--
WBR, SD.

[Bhavbhuti eBooks]

and I almost made it :)

Thanks, will keep that in mind

Kind regards

Bhavbhuti