Trigger Help

[orakle]

Hi all,

I have a Table A

Columns are

ID F_name Lname Oracleaccount Updated_by Updated_date
--- ----- -------- -------------- ----------- ------------
1 Scott Tiger scott
2 Kim Tylor Ktylor

So what i need to do is

Suppose User "Scott" Loogs into oracle and updated record

ID F_name Lname Oracleaccount Updated_by Updated_date
--- ----- -------- -------------- ----------- ------------
1 Scott Tiger scott
2 Kim roger Kroger 1 05-MAY-2003


I need the User's id number in Table A to be placed automatically when he updates
record in table and Sysdate into updated_date

I created a Procedure and Trigger on table A

Procedure

CREATE OR REPLACE PROCEDURE updateby(
CUDATE out DATE,
CUBY out NUMBER)
IS
BEGIN
SELECT SYSDATE, CTUT_ID
INTO CUDATE,CUBY
from A
where oracleaccount = USER;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'User Does not Exist');
END PR_TDBX_CUDATEBY;


Trigger

CREATE OR REPLACE TRIGGER TU_A
BEFORE INSERT ON A
FOR EACH ROW
BEGIN
updateby(:NEW.UPDATED_DATE,
:NEW.UPDATED_BY);
END;

I am getting a mutating error.I checkd so many doucments it didnt help me

I am poor in procedures and triggers

any code is helpful

Thanks

[gandolf989]

Originally posted by orakle

CREATE OR REPLACE TRIGGER TU_A
BEFORE INSERT OR UPDATE ON A
FOR EACH ROW
BEGIN
:NEW.UPDATED_DATE := USER;
:NEW.UPDATED_BY := SYSDATE;
END;

This is all you should need.
USER will be the person inserting or updating
and sysdate will be the current time.

[orakle]

Hi gandolf989,

I dont need the username

I need the ID of User in Table A

For example

USer Scott ID 1
User Kroger ID 2

So if User scott is updating records

I need his ID NUMBER which is "1" in updated_by
and Sysdate in Updated_date

Hope i explained

Thanks for your help

[gandolf989]

You can not SELECT on a table from within a trigger that the trigger is firing on unless you do it in a separate procedure with PRAGMA AUTONOMOUS TRANSACTION; You might want to rethink your database design. What is table a? Is table a your user table?

Originally posted by orakle

CREATE OR REPLACE PROCEDURE updateby(
CUDATE out DATE,
CUBY out NUMBER)
IS
PRAGMA AUTONOMOUS TRANSACTION;
BEGIN
SELECT SYSDATE, CTUT_ID
INTO CUDATE,CUBY
from A
where oracleaccount = USER;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'User Does not Exist');
END PR_TDBX_CUDATEBY;

[gandolf989]

You will of course still need the trigger.

Originally posted by orakle

CREATE OR REPLACE TRIGGER TU_A
BEFORE INSERT ON A
FOR EACH ROW
BEGIN
updateby(:NEW.UPDATED_DATE,
:NEW.UPDATED_BY);
END;
/

[orakle]

Okay this is a finacial project

We keep all the Oracle user accounts in that table

So for every oracleuser account they will assign an ID in this table

So what i need to do is i need to check that the Oracle user is existed in that table and if exits in table

We need to update columns acutomatically by his ID number in TABLE A

So i can't use
CREATE OR REPLACE TRIGGER TU_A
BEFORE INSERT OR UPDATE ON A
FOR EACH ROW
BEGIN
:NEW.UPDATED_DATE := USER;
:NEW.UPDATED_BY := SYSDATE;
END;

at your trigger :new.updated_By will get the USERNAME for example SCOTT

I need SCOTT ID NUMBER FROM TABLE A

Suppose HIS ID number is 1

So i need

:new.upadted_by = 1
:new.updated-date = sysdate

the table structure is

Create table A
(ID number,
ORACLE_USER VARCHAR2(20),
CREATED_DATE DATE,
CREATED_BY NUMBER,
UPDATED_DATE DATE,
UPDATED_BY NUMBER);

SQL> INSERT INTO A
(1,'SCOTT',NULL,NULL,NULL,NULL);
1 ROW CREATED

SQL> UPDATE TABLE A
SET ORACLE_USER = 'TIGER'
WHERE ID = 1;

IT IS GIVING ME MUTATING ERROR;

HOPE I EXPLANED

THANKS FOR YOUR HELP AND TIME

[shibha]

This will work only for insert, I have no solution for updates.

CREATE OR REPLACE TRIGGER trg_at_statement_level
after INSERT ON A
BEGIN 
update A
set 
   updated_by = 
   ( select ctut_id from A where oracleaccount = USER )
   where updated_by is null;
END;

you can always update the updayed_date in row level trigger.

[gandolf989]

Try this.

CREATE OR REPLACE PROCEDURE updateby(
    cudate OUT DATE,
    cuby   OUT NUMBER)
IS
    PRAGMA AUTONOMOUS TRANSACTION;
    user_does_not_exist EXCEPTION
BEGIN
    SELECT SYSDATE, ctut_id INTO cudate, cuby
            FROM a
            WHERE oracleaccount = USER; 
EXCEPTION 
    WHEN NO_DATA_FOUND 
    THEN
        RAISE user_does_not_exist;
    WHEN OTHERS
    THEN
        RAISE;
END updateby;
/

CREATE OR REPLACE TRIGGER tu_a
    BEFORE INSERT OR UPDATE ON a
    FOR EACH ROW 
BEGIN 
    updateby(:NEW.updated_date,
    :NEW.updated_by);
END;
/

[orakle]

Hi gandolf989,

I created a table and procedure what you gave me and created trigger it is giving me an error

But both procedure and trigger compiled

SQL> DESC SYSTEM_USER
Name Null? Type
------------------------------- -------- ----
CTUT_ID NOT NULL NUMBER
CURRT_USER_ID VARCHAR2(20)
CREATED_BY NUMBER(12)
CREATED_DATE DATE
UPDATED_BY NUMBER(12)
UPDATED_DATE DATE

SQL> SELECT * FROM SYSTEM_USER;

CTUT_ID CURRT_USER_ID CREATED_BY CREATED_D UPDATED_BY UPDATED_D
--------- -------------------- ---------- --------- ---------- ------- 2 TIGER
3 EILEEN

SQL> UPDATE SYSTEM_USER
2 SET CURRT_USER_ID = 'SCOTT'
3 WHERE CTUT_ID = 2;
UPDATE SYSTEM_USER
*
ERROR at line 1:
ORA-04091: table SYSTEM_USER is mutating, trigger/function may not see it
ORA-06512: at "UPDATEBY", line 6
ORA-06512: at "TU_SYSTEM_USER", line 2
ORA-04088: error during execution of trigger 'TU_SYSTEM_USER'

[gandolf989]

Try changing the trigger to an after trigger:

CREATE OR REPLACE TRIGGER TU_A
AFTER INSERT ON a
FOR EACH ROW 
BEGIN 
updateby(:NEW.UPDATED_DATE,
:NEW.UPDATED_BY);
END;