Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> HowTo: Trigger for Change- Logging

HowTo: Trigger for Change- Logging

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Thu, 15 Sep 2005 11:55:50 +0200
Message-ID: <1126778150.5@user.newsoffice.de>


Hi Oracle-Gurus,

there is a need to store information about changes on some tables in the database forever, that means if a user (inserts),updates or deletes a record it is to protokoll this in database. [1]

The way we want to do this is to either generate or add an existing trigger that is triggering an
(insert), update, delete - event for a determined table for each row

there must be added code that saves the changes in a ChangeLog- Table. The time/effort it would take to recreate the former data is not important, because in the special case there is a need to do this the manpower is paid.

So we decided to do the following:

Write a helper application
in which you
- choose the tables you want to observe

Trigger code looks like [3]

My questions
How to get information about some SessionParameters like V$SESSION.PROGRAM
(inside the Trigger the view v_$session and the synonym v$session cant be used, as I tried)
How can the application store data to a session? Is there only the possibility to use a table or ist there some invert- feature to sys_context() like set_sys_context('APPLICATION_USER','MOSMANN'); What do you thing in General about that? We decided in the actual case only to collect updates and deletes, and only to store old values, because there are many inserts a day but nearly no updates and deletes (delete must save all columns). Do you think it is enough?
Is it better to use a before or an after- Trigger? What happens if you f.e. use an after- trigger and the before- trigger raises an exception?

[1] Oracle 9.2.0.4, Windows
[2] TBChangeLog

CLOGID	CHAR	38	--primary key
CLOGTIME	DATE	7	--time when logging happend
CLOGIDUSER	CHAR	38	--internal- user
CLOGOSUSER	VARCHAR2	30	--os- user
CLOGAPPLICATION	VARCHAR2	30	--name of the application
CLOGTERMINAL	VARCHAR2	30	--name of the terminal
CLOGIPADDRESS	VARCHAR2	30	--ip- address of the terminal
CLOGSCHEMA	VARCHAR2	30	--schema name
CLOGTABLE	VARCHAR2	30    --table name
CLOGREASON	CHAR	1    --insert/update/delete
CDATAID	CHAR	38    --primary key of record
CDATAFIELDS	CLOB	4000    --XML- coded Changes
[3]
TRIGGER DELETESAMPLE
BEFORE DELETE
ON SAMPLESCHAME.SAMPLETABLE
FOR EACH ROW
/******************************************************************************
*
*  Title:       DELETESAMPLE
*  Created:     14.09.2005 15:28:07
*  Author:      automatisch generiert von TriggerFuerAenderungsprotokoll
*
* Description : Dieser Trigger wurde automatisch generiert, um die Änderungen
*               in Tabelle SAMPLETABLE im Schema SAMPLESCHAME in die
*               Protokolldatei TBCHANGELOG zu schreiben
*
******************************************************************************/
declare

    lvsFields varchar2(4000);

begin

    lvsFields := '';
    lvsFields:=

        PChangeLog.EncodeXML('CID',:old.CID,NULL)/*CHAR*/||
        PChangeLog.EncodeXML('CNAME',:old.CNAME,NULL)/*VARCHAR2*/||
-- some more columns
        

PChangeLog.EncodeXML('CBEMERKUNGEN',:old.CBEMERKUNGEN,NULL)/*VARCHAR2*/

    ;
    if lvsFields is not null then

        if lvsFields is not null then
            
lvsFields:='<Spalten>'||chr(13)||lvsFields||'</Spalten>'||chr(13);
        end if;

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de
Received on Thu Sep 15 2005 - 04:55:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US