bigjim-network

Here is a way to know « when » and « who » change datas in a table. This mothed imply to four collums to your table:

1. Two for users that have created or modified datas in a row : USERCREA, USERMOD
2. Two mores to store dates of changes : DATECREA, DATEMOD

USERCREA and DATECREA will be filled simply with default values of columns and have to be declared this way:

• USERCREA VARCHAR2(20 BYTE) Yes SYS_CONTEXT(‘USERENV’,'OS_USER’)

This will search the current os’s user of the client.

• DATECREA DATE Yes SYSDATE

Sysdate simply return the actual date of the server

That show that some functions could be used as default value in a column but do not expect to much of that as users function could not be used. As I know, only sysdate and SYS_CONTEXT work but here is some other intersting stuff:

• – host varchar2(256) default SYS_CONTEXT(‘USERENV’,'HOST’)
• ip_address varchar2(256) default SYS_CONTEXT(‘USERENV’,'IP_ADDRESS’)
• language varchar2(256) default SYS_CONTEXT(‘USERENV’,'LANGUAGE’)
• protocol varchar2(200) default SYS_CONTEXT(‘USERENV’,'NETWORK_PROTOCOL’)
• terminal varchar2(200) default SYS_CONTEXT(‘USERENV’,'TERMINAL’)

Next to that we will need a trigger to fill USERMOD and DATEMOD columns :

CREATE OR REPLACE TRIGGER "TRIGGER_YOURTABLE_UPDATE"
BEFORE UPDATE
ON YOURTABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
v_OSUSER VARCHAR2(25);
BEGIN
v_OSUSER := SYS_CONTEXT('USERENV','OS_USER');

:NEW.DATEMOD := SYSDATE;
:NEW.USERMOD := v_OSUSER;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_YOURNAME_UPDATE;
ALTER TRIGGER « MRMRS ». »TRIGGER_YOURTABLE_UPDATE » ENABLE

Now you have four columns letting you know at any time who and when your datas have been created or updated.