X-Received: by 2002:a05:6214:5e93:b0:647:1ef1:cea4 with SMTP id mm19-20020a0562145e9300b006471ef1cea4mr117384qvb.6.1694581387331; Tue, 12 Sep 2023 22:03:07 -0700 (PDT)
X-Received: by 2002:a05:6830:1d77:b0:6b9:97f6:655 with SMTP id l23-20020a0568301d7700b006b997f60655mr422680oti.2.1694581387003; Tue, 12 Sep 2023 22:03:07 -0700 (PDT)
Path: csiph.com!1.us.feeder.erje.net!feeder.erje.net!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer02.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ingres
Date: Tue, 12 Sep 2023 22:03:06 -0700 (PDT)
In-Reply-To: <757cd439-a568-4385-ad7e-b1bb6bf12a97n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=49.197.162.78; posting-account=dyAZ7QoAAADC1de9O1cMRM3Rqqi1tDzJ
NNTP-Posting-Host: 49.197.162.78
References: <757cd439-a568-4385-ad7e-b1bb6bf12a97n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <80ea7a3c-5dab-48ed-bc0d-cd39836ef4ecn@googlegroups.com>
Subject: Re: Triggers and locks
From: Paul White <shift7solutions@gmail.com>
Injection-Date: Wed, 13 Sep 2023 05:03:07 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 3760
Xref: csiph.com comp.databases.ingres:3897

Ciao Fabrizio,

Roy's suggestion of extracting audit journals is a fine idea.   I keep 12 m=
onths of journals for auditing and offline analysis - and sometimes for rec=
overy.

If you considered making use of a DBevent with a separate monitoring progra=
m, such as OpenROAD or ESQL/C,  it could inspect the parameters to decide t=
o 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 lis=
tens for client DBevent request.  It updates data, sends emails, kicks off =
print jobs and performs other important functions depending on various (cus=
tomisable) rules (for example customer balance > credit_limit).

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

=3D=3D Schema =3D=3D
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
) =3D
DECLARE
Lv_EventText =3D varchar(2000) not null with default;
BEGIN
Lv_EventText =3D '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


=3D=3D esql/c program =3D=3D
...
    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 =3D 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
&