Stack von Updates/Inserts auf bestimmte Tabelle "loggen"

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo zusammen,

habe eine Frage:

Ich stehe vor der Problematik herauszufinden, WO in meiner PLSQL Programmierbarkeit ein Update bzw Insert auf eine bestimmte Tabelle statt findet.

Ich habe mir etwas ähnliches wie ein Logging vorgestellt. Ich lege also eine Tmp-Tabelle an, in der ich mir alle „Insert“ und „Update“-Events auf die Tabelle logge.

Den Insert in die Tmp- Tabell müsste wahrscheinlich in einen Insert-Update-Trigger erfolgen. 

Ich versuche mir den Aufruf-Stack des Triggers in die Tmp-Tabelle zu loggen. Ich benutze die Funktion DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() dafür.

Allerdings sehe ich dann, dass der Aufrufstack nicht über den Trigger hinaus aufgezeichnet wird. 

Kennt jemand eine alternative um die Updates und Inserts zu loggen?

Danke und Gruß,

Flo

#2

–  Dies Package ist hier nicht sinnvoll DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
–   

–  Implementierung via Trigger ist am schnellsten gemacht…

– +++ clean up +++
DROP TABLE emp_test;
DROP TABLE emp_test_hist;
DROP SEQUENCE emp_test_hist_seq;

–  Take test data…
SELECT empno, ename, hiredate FROM emp

–  Oder so …

CREATE TABLE DEMO.EMP
(
  EMPNO     NUMBER(4)                           NOT NULL,
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
)

SET DEFINE OFF;

Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7839, ‚KING‘, TO_DATE(‚11/17/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7698, ‚BLAKE‘, TO_DATE(‚05/01/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7782, ‚CLARK‘, TO_DATE(‚06/09/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7566, ‚JONES‘, TO_DATE(‚04/02/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7788, ‚SCOTT‘, TO_DATE(‚12/09/1982 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7902, ‚FORD‘, TO_DATE(‚12/03/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7369, ‚SMITH‘, TO_DATE(‚12/17/1980 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7499, ‚ALLEN‘, TO_DATE(‚02/20/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7521, ‚WARD‘, TO_DATE(‚02/22/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7654, ‚MARTIN‘, TO_DATE(‚09/28/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7844, ‚TURNER‘, TO_DATE(‚09/08/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7876, ‚ADAMS‘, TO_DATE(‚01/12/1983 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7900, ‚JAMES‘, TO_DATE(‚12/03/1981 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));
Insert into DEMO.EMP
   (EMPNO, ENAME, HIREDATE)
 Values
   (7934, ‚MILLER‘, TO_DATE(‚01/23/1982 00:00:00‘, ‚MM/DD/YYYY HH24:MI:SS‘));

COMMIT;

– +++ Create test table +++
CREATE TABLE emp_test
    AS SELECT empno, ename, hiredate FROM emp

– +++ Review data +++
SELECT * FROM emp_test

– +++ Create a hist Table, with two addtional columns +++
CREATE TABLE emp_test_hist
    AS SELECT  1 emh, SYSDATE emd, e.* FROM emp_test e WHERE 1 = 2

– Wird im Trigger genutzt
CREATE SEQUENCE emp_test_hist_seq

– Drin lassen …
SELECT emp_test_hist_seq.nextval FROM DUAL

–  Ich gehe hier über den ganzen Record
–  Alternativ könnte auch über eine Hist Tabelle mit
–  id, Zeitstempel, value_old, value_new realisiert werden

CREATE OR REPLACE TRIGGER emp_test_trg
  BEFORE INSERT OR UPDATE OR DELETE ON emp_test
  REFERENCING NEW AS emp_new_rec OLD AS emp_old_rec
  FOR EACH ROW
DECLARE
  l_sysdate DATE;
  l_seq     NUMBER;
BEGIN
  l_sysdate := SYSDATE;
  /* +++ INSERTING +++ */
  IF inserting
  THEN
    NULL;
    /* +++ UPDATING +++ */
  ELSIF updating
  THEN
 
    INSERT INTO emp_test_hist
      (emh,
       emd,
       empno,
       ename,
       hiredate)
    VALUES
      (emp_test_hist_seq.nextval,
       SYSDATE,
       :emp_old_rec.empno,
       :emp_old_rec.ename,
       :emp_old_rec.hiredate);
 
  END IF; /* +++ DELETING +++ */

END dep_trg;

– Ist Trigger valid ?

SELECT * FROM user_objects where object_name = ‚EMP_TEST_TRG‘

SELECT * FROM emp_test WHERE empno = 7839  – 17-NOV-1981

UPDATE emp_test SET hiredate = SYSDATE WHERE empno = 7839

UPDATE emp_test SET hiredate = SYSDATE +1 WHERE empno = 7839

COMMIT

SELECT *
  FROM
          (
                SELECT ‚akt version‘ version, NULL AS ID , null AS dml_date, e.*
                  FROM emp_test e WHERE empno = 7839  – 17-NOV-1981
             UNION ALL
                SELECT ‚hist_version‘, h.*
                FROM emp_test_hist h WHERE empno = 7839
          )
ORDER BY id DESC NULLS FIRST, empno            

– +++ clean up +++
DROP TABLE emp_test;
DROP TABLE emp_test_hist;
DROP SEQUENCE emp_test_hist_seq;

–  Siehe auch http://www.morganslibrary.org/library.html