Regarding Trigger Help

[orakle]

Hi All,

I am using Release 9.2.0.1.0

I have a table name

SQL> desc system_user
Name Type
------------------------------- ----
ID NUMBER(12)
USER_ID VARCHAR2(30)
CREATED_BY NUMBER(12)
CREATED_DATE DATE
UPDATED_BY NUMBER(12)
UPDATED_DATE DATE

And I have data like

Sql> Select * from System_user;

ID USER_ID CREATED_BY CREATED_DATE UPDATE_BY UPDATE_DATE
-- ------ ---------- ------------ --------- -----------
1 SCOTT

My questions are like this

1) Suppose If scott is logged into Oracle and try to insert record in table syste_user what i need data is below

Sql> Select * from System_user;

ID USER_ID CREATED_BY CREATED_DATE UPDATE_BY UPDATE_DATE
-- ------ ---------- ------------ --------- -----------
1 SCOTT
2 KKIM 1 13-MAY-2003
3 TKIM 1 13-MAY-2003

2) IF scott is trying to update records in table system_user
i have to get data like this

ID USER_ID CREATED_BY CREATED_DATE UPDATE_BY UPDATE_DATE
-- ------ ---------- ------------ --------- -----------
1 SCOTT
2 TKIM 1 13-MAY-2003 1 13-MAY-2003
3 KKIM 1 13-MAY-2003 1 13-MAY-2003

I need to have triggers to be written on SYSTEM_USER Table

Any help

code is very helpful

I tried the trigger like below for inserting and updating

PROCEDURE :

CREATE OR REPLACE PROCEDURE AUDIT_COLUMNS(
CUDATE out DATE,
CUBY out NUMBER)
IS
BEGIN
SELECT SYSDATE, ID
INTO CUDATE,CUBY
from SYSTEM_USER
where USER_ID = USER;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'User Does not Exist');
END PR_TDBX_MAINTAIN_AUDIT_COLUMNS;

Trigger for Inserting :


CREATE OR REPLACE TRIGGER T_systemuser
BEFORE INSERT ON system_user
FOR EACH ROW
BEGIN
AUDIT_COLUMNS(:NEW.CREATED_DATE,
:NEW.CREATED_BY);
END;

Trigger for Updating :


CREATE OR REPLACE TRIGGER T_systemuser
BEFORE INSERT ON system_user
FOR EACH ROW
BEGIN
AUDIT_COLUMNS(:NEW.updated_DATE,
:NEW.updated_BY);
END;

I am getting ERROR

ORA-04091: table SYSTEM_USER is mutating, trigger/function may not see it
ORA-06512: at "AUDIT_COLUMNS", line 6
ORA-06512: at "SYSTEMUSER", line 2
ORA-04088: error during execution of trigger 'T_SYSTEMUSER'


Any help please I stucked with this for long time and guru van help me

Thanks

[marist89]

http://asktom.oracle.com/~tkyte/Mutate/index.html