Groups | Search | Server Info | Login | Register

Groups > comp.databases.ingres > #3897

Re: Triggers and locks

Show all headers | View raw

Ciao Fabrizio,

Roy's suggestion of extracting audit journals is a fine idea.   I keep 12 months of journals for auditing and offline analysis - and sometimes for recovery.

If you considered making use of a DBevent with a separate monitoring program, such as OpenROAD or ESQL/C,  it could inspect the parameters to decide to write to different audit tables or write to a different database or could output to text file or some other system service.

I have given some excerpts below for ESQL/C.  This code is rehashed from a production implementation which monitors changes to critical tables and listens for client DBevent request.  It updates data, sends emails, kicks off print jobs and performs other important functions depending on various (customisable) rules (for example customer balance > credit_limit).

Something to keep in mind is that you will miss events if the monitoring program is not running.

== Schema ==
create dbevent spider_audit
\p\g
grant register on dbevent "ingres".spider_audit to public
\p\g
grant raise on dbevent "ingres".spider_audit to public
\p\g

create procedure  prd_spider_update (
   audittab VARCHAR(32) NOT NULL,
   cd_cliente VARCHAR(5) NOT NULL,
   cd_doc VARCHAR(3) NOT NULL,
   orario VARCHAR(2) NOT NULL,
   nm_faldone INTEGER NOT NULL,
   nm_pratica INTEGER NOT NULL,
   gr_pratica INTEGER NOT NULL,
   tp_operazione INTEGER NOT NULL
) =
DECLARE
Lv_EventText = varchar(2000) not null with default;
BEGIN
Lv_EventText = 'INSERT INTO ' + :audittab +
   '(cd_cliente, cd_doc ,nm_faldone, nm_pratica, gr_pratica, tp_operazione, tm_archiviaz) ' +
   ' VALUES(' +
   '''' + cd_cliente + ''',' +
   '''' + cd_doc + ''',' +
   '''' + orario + ''',' +
   varchar(nm_faldone) + ',' +
   varchar(nm_pratica) + ',' +
   varchar(gr_pratica) + ',' +
   varchar(tp_operazione) + ',' +
   '''' + varchar(CURRENT_TIMESTAMP()) + ''')';

RAISE DBEVENT "ingres".spider_audit :Lv_EventText WITH SHARE;
END

\p\g


== esql/c program ==
...
    EXEC SQL CONNECT :DBNAME IDENTIFIED BY spider_audit_service;
...
    EXEC SQL register dbevent spider_audit;
...
    while (1) {
      EXEC SQL get dbevent with wait;
...
      EXEC SQL inquire_sql (:evtext = dbeventtext);
...
      if ( ! strncmp(evtext,"exit",4) ) {
        printf ( "Exit command received");
        break;
      }
      EXECUTE IMMEDIATE :evtext;
    }

ps.  your AFTER INSERT rule looks like it needs to refer to the new record rather than the old record?

Paul
&

Back to comp.databases.ingres | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Triggers and locks Fabrizio Di Renzo <fabrizio.direnzo@bucap.it> - 2023-09-12 02:40 -0700
  Re: Triggers and locks Roy Hann <specially@processed.almost.meat> - 2023-09-12 10:36 +0000
    Re: Triggers and locks G Jones <geraint.jones@ndph.ox.ac.uk> - 2023-09-12 07:41 -0700
  Re: Triggers and locks Paul White <shift7solutions@gmail.com> - 2023-09-12 22:03 -0700
    Re: Triggers and locks Fabrizio Di Renzo <fabrizio.direnzo@bucap.it> - 2023-09-12 23:53 -0700
      Re: Triggers and locks Roy Hann <specially@processed.almost.meat> - 2023-09-13 08:54 +0000

csiph-web