Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> HowTo: Trigger for Change- Logging
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]
/****************************************************************************** * * Title: DELETESAMPLE * Created: 14.09.2005 15:28:07 * Author: automatisch generiert von TriggerFuerAenderungsprotokoll*
* 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> deReceived on Thu Sep 15 2005 - 04:55:50 CDT
![]() |
![]() |