who updated the data

[knitwit]

Hi,
I'm checking on a question for my dba. She's new to Oracle (coming from SQL server). Is there a system field that saves the user that last updated a row of data? We found the sysdate to get the date and time. But what about who did the update/creation of the row?

Thanks

 

[SantaMufasa]

Knitwit,

To document WHO made WHAT changes to WHICH rows WHEN, you need a trigger that writes all this information to an change-audit table. To see all the code involved, you can click on thread759-804734, "Script to See the Before and After Update". (I just bumped it to near the top of this forum for easier location.)

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

FYI - The reserved word that identifies the user is USERID.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

The reserved word that identifies the user is USERID.

Barb, I'm not quite sure what you mean. The only Oracle key word to identify the current user (of which I am aware) is user:

SQL> select userid from dual;
select userid from dual
       *
ERROR at line 1:
ORA-00904: "USERID": invalid identifier

SQL> select user from dual;

USER
------------------------------
DHUNT

SQL>

Let me know if this differs from your experience.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

You are of course correct - not enough coffee makes for bad posts. My apologies.

When I read the original post, I interpreted it that the fledgling DBA had identified SYSDATE as the reserved word to obtain the "when", but did not know the reserved word to identify the "who". It is, of course, USER.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus