Re: log changes trigger

Hi,

Why don't you just use 'USER' ?

Your insert statement would look like

  insert into my_log
> (username, change_date, other_fields...)
> values
> (USER, sysdate, :old.other_fields...);

USER can be used in the same
way as you're using SYSDATE . It inserts the name of the logged in user performing the insert.You don't need V$SESSION at all.

Hope this helps,

Steve

In article <944j73$59h$1_at_nnrp1.deja.com>,   Jonas Malmsten <deja_at_malmsten.net> wrote:
> I need to write a trigger to log changes made to a table and by who
> these changes were made. I have created a log table having a field
> user_name varchar2(30) where I intend to store the username for anyone
> who changed the my_table, along with the previous values, before it
 was
> changed. Currently my trigger looks something like this (I dont need
 to
> log delete och insert events).
>
> create or replace trigger tr_u_my_table
> after update on my_table
> for each row
> uname varchar2(30);
> begin
> select username into uname
> from v$session where audsid = userenv('SESSIONID');
> insert into my_log
> (username, change_date, other_fields...)
> values
> (uname, sysdate, :old.other_fields...);
> end;
> /
>
> My problem is that not all users have permission to select from
> v$session. Is there another way to do this or do I need to grant
 select
> on v$session to all_users?
>
> //Jonas
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 17 2001 - 13:50:03 CST