Re: How to get UID of executing user ina trigger?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 16 Sep 1999 10:35:01 -0400
Message-ID: <G=7gN8j5S3SNUek+BWiJh7APYvqh@4ax.com>

A copy of this was sent to "Peter Laursen" < ptl_at_edbgruppen.dk> (if that email address didn't require changing) On 16 Sep 1999 14:10:12 GMT, you wrote:

>If I do :new.user_id = UID
>in a trigger, I get the UID of the owner/creater of the trigger.
>How do I get the UID of the user thats does the DML that makes the trigger
>execute?
>
>Thanks
>Peter Laursen

No you don't -- the UID is the uid of the person running the trigger, not the owner (the uid of the owner is available via userenv('schemaid')

tkyte_at_8.0> create table t ( x int );
Table created.

tkyte_at_8.0> create or replace trigger t_trigger   2 before insert on t
  3 declare
  4 n number;
  5 begin

  6          dbms_output.put_line( 'UID = '  || to_char(uid) );
  7          select userenv( 'schemaid' ) into n from dual;
  8          dbms_output.put_line( 'SCHEMAID = '  || to_char(n) );

  9 end;
 10 /
Trigger created.

tkyte_at_8.0> grant all on t to public;
Grant succeeded.

tkyte_at_8.0> insert into tkyte.t values ( 1 ); UID = 10991
SCHEMAID = 10991 1 row created.

tkyte_at_8.0> connect scott/tiger
scott_at_8.0> set serveroutput on

scott_at_8.0> insert into tkyte.t values ( 1 ); UID = 3673
SCHEMAID = 10991 1 row created.

scott_at_8.0> select uid from dual;

       UID

---

      3673

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 16 1999 - 09:35:01 CDT