Re: HowTo: Trigger for Change- Logging

Andreas Mosmann wrote:
> 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
> - choose the events (insert/update/delete)
> - choose, whether you want to save old/new/both values
> that produces script
> - to add a ChangeLog- Table [2]
> - to add a package with HelpFunctions (EncodeToXML, NewGUID ...)
> that
> - findes out the triggers that must be added/created
> that
> - produces code for triggers
>
> 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;
>
> -- for different projects the columns can differ
> -- how to get information about application?
> insert into TBCHANGELOG(
> clogid,
> clogtime,
> clogtable,
> CLOGIDUSER,
> CLOGOSUSER,
> clogschema,
> clogreason,
> cdataid,
> cdatafields
> )values(
> NewGUID(),
> sysdate,
> 'SAMPLETABLE',
> NULL,-- how can I store information to a session?
> SYS_CONTEXT('USERENV','OS_USER'),
> 'SAMPLESCHAME',
> 'D',
> :old.cid,
> lvsProgram
> );
> end if;
> end;

I don't know what you expect of this group but I had an extremely hard time understanding what you are asking and am quite sure that I still don't know.

There is nothing that prevents a trigger from querying a view. You provided no version information and no error messages. I think you need to repost your question and provide a sample trigger that is the simplest case that demonstrates the issue.

Sorry I couldn't be more helpful.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)