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;

[orakle]

It is saying

ORA-04084: cannot change NEW values for this trigger type

any idea
Thanks for your help

Thanks

[gandolf989]

I should have realized that an after trigger will not work. Here is your homework should you choose to accept it. It is possible to create a collection on rows that are being updated/inserted with the before trigger, and then use an after triger to iterate through the collection updating the records appropriately.

You will need a before row level trigger and after statement level trigger. You will also need to look at how collections work. Perhaps even create a package that has a collection that can be populated and then read by the after statement level trigger. When the after statement level trigger fires the data has already been saved and you should be able to make changes, but you can no longer access the :NEW and :OLD meta rows because the transactions will be over. Thus you need something external to the trigger to store what happened hence the collection.

Here is a link with some information on collections:

PL/SQL User's Guide and Reference (http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#19388)

[gandolf989]

You can also look at an instead of trigger.

[orakle]

Instead trigger can be used only on views

I am just guessing

Any code is helpful

Thanks

[gandolf989]

I must have been thinking of Sql Server. Take a look at collections though, they will probably do the trick.