Trigger der anderen Art: Database Change Notification

English title: The other kind of 'trigger': Database Change Notification

Seit Oracle10g gibt es neben dem Trigger eine weiter Art und Weise, auf Änderungen in der Datenbank zu reagieren: Database Change Notification. In Oracle11g wurde der Name dann (weil die Funktionalität erweitert wurde) auf Continuous Query Notification geändert.

In Oracle10g Database Change Notification was introduced. This is (beyond triggers) another interesting technology to react on changes to database objects. The functionality was further developed in Oracle11g and the name was also changed to Continuous Query Notification.

Eine Change Notification kann auf ein DDL- oder DML-Ereignis eingerichtet werden (wie bei einem Trigger). Ein wesentlicher Unterschied ist allerdings, dass ein DML-Ereignis erst nach dem Commit erkannt und verarbeitet wird - das ist anders als bei einem Trigger, der sofort nach dem DML-Kommando feuert. Damit ist die Database bzw. Continuous Change Notification auch geeignet, um (basierend auf einem DML-Ereignis) eine Aktion außerhalb des Transaktionskontext der Datenbank durchzuführen, bspw. eine Mail zu versenden ...

A change notification can be configured to fire on DML as well as on DDL events. An important difference to triggers is indeed that the change notification fires after the COMMIT and not just after the statement. Therefore the change notification technology is also suitable to notify systems outside the database about changes to database object. An example for this is sending emails which you should never do with an ordinary trigger. The trigger fires just after the affected row or after the statement; it does not wait for the COMMIT. So when a ROLLBACK is issued instead the email sent is based on a phantom. But you can use the Change Notification technology since it fires DML events after the COMMIT.

Der andere wesentliche Unterschied ist die Art und Weise, wie die Change Notification eingerichtet wird. Auch hier gibt es ein paar fundamentale Unterschiede zu Triggern. Als Beispiel soll eine Change Notification wie folgt eingerichtet werden: Sobald die Ergebnismenge einer Abfrage auf die Tabellen EMP und DEPT sich ändern würde, sollen die betroffenen Tabellen und ROWIDs in der Tabelle TABLE_LOG geloggt werden. Bevor wir beginnen, müssen wir allerdings sicherstellen, dass der Datenbankuser, mit dem wir arbeiten, alle nötigen Privilegien hat.

The other big difference in comparison to triggers is the kind of setup for a Change Notification. I'll try an example: I want to have an event fired when the result set of a specific query (based on the tables EMP and DEPT) would change due to a DML action. Then the affected table name and rowid's shall be logged in the new table TABLE_LOG. Before starting we'll make sure that the database user we're working with has the correct privileges.

SQL> grant change notification to [schema];
SQL> grant execute on DBMS_CQ_NOTIFICATION to [schema];

Außerdem muss der Init-Parameter job_queue_processes auf einem Wert größer als Null (0) stehen

Furthermore the init-parameter job_queue_processes must have a value greater than zero.

SQL> alter system set job_queue_processes=10 scope=both;

Dann beginnen wir mit dem Erstellen der Logging-Tabelle.

Then I start with creating the logging table.

create table table_log (
  tabelle   varchar2(30),
  row_id    rowid
)
/

Nun wird die PL/SQL-Prozedur erstellt, die beim Auftreten des Ereignisses ausgeführt wird. Wichtig ist die Signatur der Prozedur - die muss aussehen wie hier vorgestellt.

After that I create a PL/SQL procedure which should be executed when the event is being fired. The signature is important - it has to look exactly like here.

CREATE OR REPLACE PROCEDURE mein_callback (
  ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR
) IS
  v_regid           NUMBER;
  v_event_type      NUMBER;
  v_operation_type  NUMBER;
  v_numtables       NUMBER          := 0;
  v_tbname          VARCHAR2(60);
  v_numrows         NUMBER;
  v_row_id          VARCHAR2(2000);
  v_numqueries      NUMBER          := 0;
BEGIN
 v_event_type := ntfnds.event_type;
 IF (v_event_type = DBMS_CQ_NOTIFICATION.EVENT_QUERYCHANGE) THEN
  -- determine affected registered queries 
  v_numqueries := ntfnds.query_desc_array.count;
  FOR i in 1..v_numqueries LOOP
   -- determine affected tables
   v_numtables := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array.count;
   FOR j IN 1..v_numtables LOOP
    -- get table name
    v_tbname := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array(j).table_name;
    v_operation_type := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array(j).Opflags;
    IF (bitand(v_operation_type, DBMS_CQ_NOTIFICATION.ALL_ROWS) = 0) THEN
     v_numrows := ntfnds.query_desc_array(i).table_desc_array(j).numrows;
    ELSE
     v_numrows :=0;   -- ROWID INFO NOT AVAILABLE 
    END IF;
 
    FOR k IN 1..v_numrows LOOP
     v_row_id := ntfnds.query_desc_array(i).table_desc_array(j).row_desc_array(k).row_id;
     INSERT INTO table_log (tabelle, row_id) values (v_tbname, v_row_id);
    END LOOP;  
   END LOOP;     
  END LOOP;        
 END IF;
END;
/

Diese Prozedur wird im nächsten Schritt in der Datenbank zusammen mit einer Abfrage registriert. Immer wenn eine Änderung an Datenbankobjekten (Tabellen) die Ergebnisse der Abfrage ändern würde, wird die Prozedur aufgerufen. Wie man sehen kann, nimmt sie genau einen Parameter entgegen - und zwar vom Typ CQ_NOTIFICATION$_DESCRIPTOR. Dies ist ein Objekttyp; wenn man sich den genauer ansieht ...

In the next step I'll register this procedure with a SQL query. Each time the query result set changes this procedure should be called. Notice the single parameter ntfnds which acts as a context object. It is of type CQ_NOTIFICATION$_DESCRIPTOR which looks like this:

SQL> desc  CQ_NOTIFICATION$_DESCRIPTOR
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------
 REGISTRATION_ID                                    NUMBER
 TRANSACTION_ID                                     RAW(8)
 DBNAME                                             VARCHAR2(30)
 EVENT_TYPE                                         NUMBER
 NUMTABLES                                          NUMBER
 TABLE_DESC_ARRAY                                   SYS.CHNF$_TDESC_ARRAY
 QUERY_DESC_ARRAY                                   SYS.CHNF$_QDESC_ARRAY

... sieht mann, dass die Information über das Ereignis, welche zum Aufruf der Prozedur geführt hat, komplett da drin steckt. Der obige Code liest das Objekt aus, stellt fest, welche Tabellen und welche Zeilen in den Tabellen betroffen sind und trägt sie in die Tabelle TABLE_LOG ein. Was in dieser Prozedur drinsteckt, ist völlig dem Entwickler (also euch) überlassen - da die Change Notification euch nur über bereits "Committete" Änderungen unterrichtet, könnt Ihr auch Mails versenden oder (in größeren Systemen) andere Komponenten benachrichtigen. So ließe sich auch in der Mittelschicht verwalteter Cache für Datenbankinhalte über Änderungen benachrichtigen.

All information about the event which fired a procedure call is embedded within this object. The code therefore traverses the object, extracts the affected table names and row ids and logs them in the TABLE_LOG table. It's completely up to the developer what's in this procedure. You can rely that a DML event which fires the procedure is already committed - so you can perform calls outside the transaction context of the database. Examples are sending Emails or notifications to mid-tier-compontents (perhaps to maintain a midtier cache).

So - und nun registrieren wir die Prozedur. Sie soll stets aufgerufen werden, wenn die Ergebnismenge für folgende Query sich ändert.

Ok - now I'll register the procedure - it should be called each time the result set of this query changes.

select 
  e.empno,
  e.ename,
  e.sal,
  d.dname,
  d.loc
from emp e, dept d 
where e.deptno = d.deptno and e.sal > 2000;

Und das geht wie folgt ...

And this is done as follows ...

DECLARE
  v_reginfo  cq_notification$_reg_info;
  v_regid    NUMBER;
  v_cursor   SYS_REFCURSOR;
BEGIN
  -- Construct registration info object
  v_reginfo := CQ_NOTIFICATION$_REG_INFO (
    '[Schema].MEIN_CALLBACK',                 -- PL/SQL notification handler
    DBMS_CQ_NOTIFICATION.QOS_QUERY           -- notification type QRCN
    + DBMS_CQ_NOTIFICATION.QOS_ROWIDS,       -- include rowids of changed objects
    0,                                       -- registration persists until unregistered
    0,                                       -- notify on all operations
    0                                        -- notify immediately
  );
  
  -- Start registration process 
  v_regid := DBMS_CQ_NOTIFICATION.NEW_REG_START(
    regds => v_reginfo
  );

  -- Execute queries ...
  OPEN v_cursor FOR 
   select 
     e.empno,
     e.ename,
     e.sal,
     d.dname,
     d.loc
   from emp e, dept d 
   where e.deptno = d.deptno and e.sal > 2000;
  CLOSE v_cursor;

  -- Finish registration process
  DBMS_CQ_NOTIFICATION.REG_END;
END;
/

Nun können wir ausprobieren ... Zunächst sehen wir uns an, was wir denn ändern müssen, um überhaupt eine Benachrichtigung auszulösen ...

Let's test: First I have a look at the current result set of the query ...

     EMPNO ENAME             SAL DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7566 JONES            2975 RESEARCH       DALLAS
      7698 BLAKE            2850 SALES          CHICAGO
      7782 CLARK            2450 ACCOUNTING     NEW YORK
      7788 SCOTT            3000 RESEARCH       DALLAS
      7839 KING             5000 ACCOUNTING     NEW YORK
      7902 FORD             3000 RESEARCH       DALLAS

Also ändern wir mal ... die Lokation des Departments RESEARCH in Trier um.

And then I do an update which affects the result set.

SQL> update dept set loc = 'TRIER' where dname = 'RESEARCH';

1 row updated.

Nachsehen in der Tabelle TABLE_LOG ...

Did the event fire the procedure ...?

SQL> select * from table_log;

No rows selected.

Ach ja: Die Change Notification feuert ja erst beim COMMIT. Also ...

Oh yes - the Change Notification fires after the COMMIT ...

SQL> commit;

COMMIT complete.

SQL> select * from table_log;

TABELLE                        ROW_ID
------------------------------ ------------------
PARTNER.DEPT                   AAApb4AAEAAANxkAAB

Wenn man Änderungen an Zeilen vornimmt, die keinen Einfluß auf die Ergebnismenge obiger Abfrage haben, passiert nichts. Also eine ganz interessante Art und Weise, auf Änderungen in der Datenbank zu reagieren.

If the DML operation affects only rows not relevant for the query's result set there is no action. So this is a pretty interesting piece of technology.

Naja, ein paar Dictionary Views gibt es auch noch - möchte man sich die vorhandenen Registrierungen für Abfragen ansehen, so gibt die View USER_CQ_NOTIFICATION_QUERIES Auskunft ...

There are also some dictionary views. If you want to know whether there are callback registrations for queries have a look in USER_CQ_NOTIFICATION_QUERIES.

SQL> SELECT queryid, regid, TO_CHAR(querytext) query FROM user_cq_notification_queries;

   QUERYID      REGID QUERY
---------- ---------- ----------------------------------------
         2          5 SELECT PARTNER.DEPT.LOC , PARTNER.DEPT.
                      DNAME , PARTNER.EMP.EMPNO , PARTNER.EMP.
                      ENAME , PARTNER.EMP.SAL  FROM PARTNER.DE
                      PT , PARTNER.EMP  WHERE  ( PARTNER.EMP.S
                      AL  > 2000  AND  PARTNER.EMP.DEPTNO  = P
                      ARTNER.DEPT.DEPTNO  )

... während die View USER_CHANGE_NOTIFICATION_REGS über die Registrierung im Allgemeinen Auskunft gibt - insbesondere steht da die Callback-Funktion drin.

And another view, USER_CHANGE_NOTIFICATION_REGS gives information about the registration itself - here you can see the PL/SQL procedure which is registered with the query.

SQL> select REGID, CALLBACK, TABLE_NAME from user_CHANGE_NOTIFICATION_REGS

     REGID CALLBACK                            TABLE_NAME
---------- ----------------------------------- --------------------
         5 plsql://PARTNER.MEIN_CALLBACK?PR=0  PARTNER.EMP
         5 plsql://PARTNER.MEIN_CALLBACK?PR=0  PARTNER.DEPT

Und wie wird man eine Registrierung wieder los? Auch dafür gibt es einen Aufruf ...

And how to delete a change notification registration? There is a procedure available ...

begin
  DBMS_CQ_NOTIFICATION.DEREGISTER(5);
end;

Ihr müsst die Registrierungs-ID (hier: 5) übergeben, um die entsprechende Registrierung zu löschen. Weitere Informationen findet Ihr in den Handbüchern: Zunächst wäre der Advanced Application Developers' Guide zu nennen; Infos über das beteiligte PL/SQL-Paket DBMS_CQ_NOTIFICATION findet Ihr in der PL/SQL Packages and Types Reference.

You have to provide the ID of the change notification registration to the DEREGISTER call. More information in the documentation: The Advanced Application Developers' Guide contains generic information about the change notification technology; the PL/SQL Packages and Types Reference contains reference information for the PL/SQL package DBMS_CQ_NOTIFICATION which was used to perform the registration.