Re: HowTo: Trigger for Change- Logging

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 15 Sep 2005 12:01:08 -0700
Message-ID: <1126810815.866553@yasure>

Andreas Mosmann wrote:
> 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

Not angered ... confused.

The error you are getting is very specific and clear. You do not have the required privilege on v_$session.

If your access is granted via a role that is insufficient. You must grant explicitly for PL/SQL.

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

Received on Thu Sep 15 2005 - 14:01:08 CDT