Re: HowTo: Trigger for Change- Logging

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Thu, 15 Sep 2005 17:53:32 +0200
Message-ID: <1126799612.38@user.newsoffice.de>

DA Morgan schrieb am 15.09.2005 in <1126795191.588721_at_yasure>:

> 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.

Sorry, I didn't want to anger you. I try to short this, but it will be difficult. If I miss too much information maybe the whole will not be understandable.

My destination:
  write triggers, that log changes on some important tables shortened but
  with some information about the changer into a table TBChaneLog

Used version:
>> [1] Oracle 9.2.0.4, Windows

table changeLog:
>> [2] TBChangeLog
>> CLOGID CHAR 38 --primary key
>> CLOGTIME DATE 7 --time when logging happend
>> CLOGIDUSER CHAR 38 --internal- user

and some more data describing Application/User

>> CDATAID CHAR 38 --primary key of record
>> CDATAFIELDS CLOB 4000 --XML- coded Changes

trigger:
>> [3]
>> TRIGGER DELETESAMPLE
>> BEFORE DELETE
>> ON SAMPLESCHAME.SAMPLETABLE
>> FOR EACH ROW
>>
>> declare
>> lvsFields varchar2(4000);
>>
>> begin
>> lvsFields := '';
>> lvsFields:=
>> PChangeLog.EncodeXML('CID',:old.CID,NULL)||
>> -- some more columns
>> PChangeLog.EncodeXML('CBEMERKUNGEN',:old.CBEMERKUNGEN,NULL) ;

>> 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,

some information
>> cdataid,
>> cdatafields
>> )values(
>> NewGUID(),
>> sysdate,

some information
>> :old.cid,
>> lvsProgram
>> );
>> end if;
>> end;

the Function
PChangeLog.EncodeXML
is especially written for this triggers.

>> 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)

> 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.

if I insert the statement
  select
    program
  into
    lvsProgram
  from
    v$session
  where
    audsid=sys_context('USERENV','SESSIONID'); i got the compiler error
"ORA 00942: Table or View doesn't exist" at the position from "v$session"
I tried v_$session but the same result.
Outside of the trigger (that is owned by USERX) USERX can select v$session as well as v_$session.
Must triggers be given any privilegs on v$session?

>> 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');
If an Application on start for each session some data, for instance Application Name and Version or User Data I could query this in a trigger.
The only way I already know is to create a table and the application puts a record containing session-ID and the mentioned data. But this seems a little oversized and there is a problem of cleaning this table if application/session crashes.
Is there a better way?

>> What do you thing in General about that?
about Logging changes into another table initialized by triggers?

>> 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?

> Sorry I couldn't be more helpful.
I am sure it is not easy to understand my English, sorry. I hope some things are clearer now?

Andreas

I know that it is always difficult to discuss concepts if at least one of the members doesn't know the used language passably. For me it is hard to read text with orthografical errors or slang (because my dictionary doesn't know) and idioms, because a translation word by word is mostly confusing.
And if I try to translate my ideas into f.e. English the problem is a little higher, because the idiom I use (or the connection between words) are misunderstandable too.
So it would be nice if you, in that case you have a hypothesis what I meant, can repeat this in your words. So I can correct or reject this.

Thank you very much

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Thu Sep 15 2005 - 10:53:32 CDT