| USERENV, and OS_USER [message #670171] |
Wed, 13 June 2018 10:26  |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
My goal is to store the os user of the person who is inserting into the table
create table mytable(myid number, create_user varchar2(40))
CREATE OR REPLACE TRIGGER trig_mytab
BEFORE INSERT OR UPDATE
ON mytable
FOR EACH ROW
DECLARE
v_user varchar2(30);
BEGIN
select substr(sys_context( 'USERENV', 'OS_USER' ),1,25)
INTO v_user
from dual;
END;
insert into mytable (myid) values(1);
commit;
when I select * from mytable, why do I see a blank in create_user ?
|
|
|
|
| Re: USERENV, and OS_USER [message #670172 is a reply to message #670171] |
Wed, 13 June 2018 10:40  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Because the trigger doesn't set create_user.
It sticks the value in a local variable v_user and then does nothing with it.
Oracle isn't going to modify the contents of a column unless you actually tell it to.
|
|
|
|
|
|
| Re: USERENV, and OS_USER [message #670174 is a reply to message #670173] |
Wed, 13 June 2018 11:09 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Very inefficient code:
CREATE OR REPLACE
TRIGGER biur_mytab
BEFORE INSERT
OR UPDATE
ON mytable
FOR EACH ROW
BEGIN
:new.create_user := substr(sys_context('USERENV','OS_USER'),1,25);
END;
/
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
