Help on update

[orakle]

Hi All ,
I am trying to INSERT updated_by and Updated_date with ctut_id and sysdate
When user trying to update any record.

IF USER scott is logged into system and trying to update table
Then I need to get his id number(CTUT_ID) into updated_by
And sysdate into updated_date

Suppose
SQL> select * from ora_system_user;

CTUT_ID CURRENT_USER_ID CREATED_BY CREATED_D UPDATED_BY UPDATED_D
--------- ------------------------------ ---------- --------- ---------- ---------
117 KKING 117 15-MAY-03
118 SCOTT 117 15-MAY-03
119 EXAMPLE1 117 15-MAY-03 117 15-MAY-03
120 EXAMPLE2 117 15-MAY-03 118 15-MAY-03
121 EXAMPLE3 117 15-MAY-03 117 15-MAY-03


Here is my code

SQL> DESC ORA_SYSTEM_USER
Name Null? Type
------------------------------- -------- ----
CTUT_ID NOT NULL NUMBER(12)
CURRENT_USER_ID NOT NULL VARCHAR2(30)
CREATED_BY NUMBER(12)
CREATED_DATE DATE
UPDATED_BY NUMBER(12)
UPDATED_DATE DATE


• PLSQL temporary tables are contained in a package
• To ensure that variables are global is to put in a package

CREATE OR REPLACE PACKAGE SYSTEMUSER as
TYPE t_UPDATED_BY is TABLE OF ora_system_user.UPDATED_BY%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_UPDATED_DATE is TABLE OF ora_system_user.UPDATED_DATE%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_CTUT_ID is TABLE OF ora_system_user.CTUT_ID%TYPE
INDEX BY BINARY_INTEGER;

U_SYSTEMUPDATEDBY T_UPDATED_BY;
U_SYSTEMUPDATEDDATE T_UPDATED_DATE;
U_SYSTEMCTUTID T_CTUT_ID;
U_NUMENTRIES BINARY_INTEGER := 0;
END PA_TDBX_UPD_ORASYSTEMUSER;



• This Trigger will use to insert the values for Created_date and Created_by columns
• A counter variable V_NUMENTRIES is used .
• So that Statement level trigger references it and then resets to zero after processing.
• This is necessary ,so that next statement issued by this session will have the correct value.

CREATE OR REPLACE TRIGGER TIBU_SYSTEMUSER
BEFORE UPDATE ON ORA_SYSTEM_USER
FOR EACH ROW
BEGIN
IF :NEW.CREATED_DATE <> :OLD.CREATED_DATE
THEN
:NEW.CREATED_DATE := :OLD.CREATED_DATE;
END IF;
IF
:NEW.CREATED_BY <> :OLD.CREATED_BY THEN
:NEW.CREATED_BY := :OLD.CREATED_BY;
END IF;
:NEW.UPDATED_DATE := ‘1-JAN-2003’;
:NEW.UPDATED_BY := 0;
SYSTEMUSER.U_NUMENTRIES :=
SYSTEMUSER.U_NUMENTRIES + 1;
SYSTEMUSER.U_SYSTEMUPDATEDBY(SYSTEMUSER.U_NUMENTRIES) := :NEW.UPDATED_BY;
SYSTEMUSER.U_SYSTEMUPDATEDDATE(SYSTEMUSER.U_NUMENTRIES) := :NEW.UPDATED_DATE;
SYSTEMUSER.U_SYSTEMCTUTID(SYSTEMUSER.U_NUMENTRIES) := :NEW.CTUT_ID;
END TIBU_SYSTEMUSER;
/












This Trigger is used to set the columns Created_by, Created_date when inserting values

CREATE OR REPLACE TRIGGER TIAU_ORASYSTEMUSER
AFTER UPDATE ON ORA_SYSTEM_USER
DECLARE
U_ID NUMBER;
U_CTUTID ORA_SYSTEM_USER.CTUT_ID%TYPE;
U_CREATEDBY ORA_SYSTEM_USER.CREATED_BY%TYPE;
U_CREATEDDATE ORA_SYSTEM_USER.CREATED_DATE%TYPE;
CUBY NUMBER(12);
BEGIN
FOR U_LOOPINDEX IN 1..SYSTEMUSER.U_NUMENTRIES LOOP
U_CTUTID := SYSTEMUSER.U_SYSTEMCTUTID(V_LOOPINDEX);
U_CREATEDBY := SYSTEMUSER.U_SYSTEMCREATEDBY(V_LOOPINDEX);
U_CREATEDDATE := SYSTEMUSER.U_SYSTEMCREATEDDATE(V_LOOPINDEX);
SELECT COUNT(*) INTO U_ID
FROM ORA_SYSTEM_USER
WHERE CURRENT_USER_ID = USER;
IF U_ID > 0 THEN
SELECT CTUT_ID INTO CUBY
FROM ORA_SYSTEM_USER
WHERE CURRENT_USER_ID = USER;
UPDATE ORA_SYSTEM_USER
SET CREATED_DATE = SYSDATE,
CREATED_BY = CUBY
WHERE CTUT_ID = U_CTUTID;
ELSE
RAISE_APPLICATION_ERROR(-20000,'NO USER FOUND IN TABLE');
END IF;
END LOOP;
SYSTEMUSER.U_NUMENTRIES := 0;
END TIAU_ORASYSTEMUSER;
I am getting the following errors

SQL> UPDATE ORA_SYSTEM_USER
2 SET CURRENT_USER_ID = 'EXAMPLE10'
3 WHERE CTUT_ID = 119;
UPDATE ORA_SYSTEM_USER
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "TIAU_ORASYSTEMUSER", line 13
ORA-04088: error during execution of trigger 'KKISHORE.TIAU_ORASYSTEMUSER'
ORA-06512: at " TIAU_ORASYSTEMUSER", line 20
ORA-04088: error during execution of trigger 'KKISHORE.TIAU_ORASYSTEMUSER'
ORA-06512: at " TIAU_ORASYSTEMUSER", line 20
ORA-04088: error during execution of trigger 'KKISHORE.TIAU_ORASYSTEMUSER'
ORA-06512: at "TIAU_ORASYSTEMUSER", line 20
ORA-04088: error during execution of trigger 'KKISHORE.TIAU_ORASYSTEMUSER'
ORA-06512: at " TIAU_ORASYSTEMUSER", line 20
ORA-04088: error during execution of trigger 'KKISHORE.TIAU_ORASYSTEMUSER'
ORA-06512: at "TIAU_ORASYSTEMUSER", line 20
ORA-04088: error during execution of trigger 'KKISHORE.TIAU_ORASYSTEMUSER'
ORA-06512: at "TIAU_ORASYSTEMUSER", line 20
ORA-04088:9 8Hi All ,

[chrisrlong]

What in the world is all this code????
when somebody logs on, do this:

GLOBAL_PKG...
   SELECT
      CTUT_ID
   INTO
      g_CTUT_ID
   FROM
      ORA_SYSTEM_USER
   WHERE
      CURRENT_USER_ID   =   SYS_CONTEXT('USERENV', 'CURRENT_USER');
END GLOBAL_PKG...

Then you also have...

CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BIR_TR
BEFORE
   INSERT
ON
   ORA_SYSTEM_USER
FOR
   EACH ROW
BEGIN
   :NEW.CREATED_BY     := GLOBAL_PKG.CTUT_ID;
   :NEW.CREATED_DATE   := SYSDATE           ;
END ORA_SYSTEM_USER_BIR_TR;

CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BUR_TR
BEFORE
   UPDATE
ON
   ORA_SYSTEM_USER
FOR
   EACH ROW
BEGIN
   :NEW.UPDATED_BY     := GLOBAL_PKG.CTUT_ID;
   :NEW.UPDATED_DATE   := SYSDATE           ;
END ORA_SYSTEM_USER_BIR_TR;

What am I missing?

- Chris

[orakle]

Can you give me xact code for package

I am new to oracle

Thanks for your help

[chrisrlong]

If you can't code a simple package, what in the world are you doing trying to code around the mutating table issue? Crawling before running and all that...

CREATE OR REPLACE PACKAGE GLOBAL_PKG
AS
   ...
   g_CTUT_ID	ORA_SYSTEM_USER.CTUT_ID%TYPE;
   ...
PROCEDURE SetCTUT_ID;
END;
CREATE OR REPLACE PACKAGE BODY GLOBAL_PKG
AS
BEGIN
...
PROCEDURE SetCTUT_ID
IS
BEGIN
   SELECT
      CTUT_ID
   INTO
      g_CTUT_ID
   FROM
      ORA_SYSTEM_USER
   WHERE
      CURRENT_USER_ID   =   SYS_CONTEXT('USERENV', 'CURRENT_USER');
END;
...
END GLOBAL_PKG;

Just call GLOBAL_PKG.SetCTUT_ID after login from the application or through a login trigger.

- Chris

(and exact has an E)

[orakle]

It is giving that no fucntion setctutid exists in scope

PACKAGE global_package IS

g_CTUT_ID ORA_SYSTEM_USER.CTUT_ID%TYPE;

PROCEDURE SetCTUT_ID;
END;

PACKAGE BODY global_package
AS
PROCEDURE SetCTUT_ID
IS
BEGIN
SELECT
CTUT_ID
INTO
g_CTUT_ID
FROM
ORA_SYSTEM_USER
WHERE
CURRENT_USER_ID = user;
END;

END ;

CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BIR_TR
BEFORE
INSERT
ON
ORA_SYSTEM_USER
FOR
EACH ROW
BEGIN
:NEW.CREATED_BY := GLOBAL_Package.CTUT_ID;
:NEW.CREATED_DATE := SYSDATE ;
END ORA_SYSTEM_USER_BIR_TR;

CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BUR_TR
BEFORE
UPDATE
ON
ORA_SYSTEM_USER
FOR
EACH ROW
BEGIN
:NEW.UPDATED_BY := GLOBAL_Package.CTUT_ID;
:NEW.UPDATED_DATE := SYSDATE ;
END ORA_SYSTEM_USER_BIR_TR;

[chrisrlong]

:NEW.CREATED_BY := global_package.g_CTUT_ID;
...
:NEW.UPDATED_BY := global_package.g_CTUT_ID;


- Chris

[orakle]

updated_date is working

but updated_by value is coming null

Thanks

[orakle]

Wrote file afiedt.buf

1 CREATE OR REPLACE PACKAGE BODY global_package
2 AS
3 PROCEDURE SetCTUT_ID
4 IS
5 g_ctut_id number(12);
6 BEGIN
7 SELECT
8 CTUT_ID,sysdate
9 INTO
10 g_CTUT_ID
11 FROM
12 ORA_SYSTEM_USER
13 WHERE
14 CURRENT_USER_ID = USER;
15 END;
16* END ;
SQL> /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY GLOBAL_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/65530 PL/SQL: SQL Statement ignored
12/4 PL/SQL: ORA-00947: not enough values

[orakle]

SORRY ALL PACKAGE AND TRIGGERS COMPILED FINE

BUT I AM GETTING ONLY SYSDATE IN UPDATE_DATE AND CREATED_DATE

THE UPDATED_BY AND CREATED_BY IS SHOWING NULL

ANY HELP PLEASE

[orakle]

HERE IS TABLE STRUCTURE

CREATE TABLE ORA_SYSTEM_USER
(CTUT_ID NUMBER(12) NOT NULL,
CURRENT_USER_ID VARCHAR2(20) NOT NULL,
CREATED_BY NUMBER(12) not null,
CREATED_DATE DATE NOT NULL,
UPDATED_BY NUMBER(12),
UPDATE_DATE DATE);

INSERT INTO ORA_SYSTEM_USER(
CTUT_ID,CURRENT_USER_ID,CREATED_BY,CREATED_DATE)
VALUES(1,'SCOTT',1,'15-MAY-2003');

SQL> SELECT * FROM ORA_SYSTEM_USER;

CTUT_ID CURRENT_USER_ID CREATED_BY CREATED_D UPDATED_BY UPDATED_D
--------- ------------------------------ ---------- --------- ---------- ---------
1 SCOTT 1 15-MAY-03


HERE IS CODE

CREATE OR REPLACE PACKAGE global_package IS

g_CTUT_ID ORA_SYSTEM_USER.CTUT_ID%TYPE;

PROCEDURE SetCTUT_ID;
END;

CREATE OR REPLACE PACKAGE BODY global_package
AS
PROCEDURE SetCTUT_ID
IS
BEGIN
SELECT
CTUT_ID,sysdate
INTO
g_CTUT_ID,g_cdate
FROM
ORA_SYSTEM_USER
WHERE
CURRENT_USER_ID = USER;
END;

END ;

CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BIR_TR
BEFORE
INSERT
ON
ORA_SYSTEM_USER
FOR
EACH ROW
BEGIN
:NEW.CREATED_BY := GLOBAL_PACKAGE.G_CTUT_ID;
:NEW.CREATED_DATE := SYSDATE ;
END ORA_SYSTEM_USER_BIR_TR;

CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BUR_TR
BEFORE
UPDATE
ON
ORA_SYSTEM_USER
FOR
EACH ROW
BEGIN
:NEW.UPDATED_BY := GLOBAL_PACKAGE.G_CTUT_ID;
:NEW.UPDATED_DATE := SYSDATE ;
END ORA_SYSTEM_USER_BIR_TR;

sql> show user
"scott"

sql> insert into ora_system_user
(ctut_id,current_user_id)
values(2,'kking');

SQL> SELECT * FROM ORA_SYSTEM_USER;

CTUT_ID CURRENT_USER_ID CREATED_BY CREATED_D UPDATED_BY UPDATED_D
--------- ------------------------------ ---------- --------- ---------- ---------
1 SCOTT 1 15-MAY-03
2 kking 15-MAY-03


CREATED_BY IS NOT WORKING.

ANY HELP PLEASE