Help on update

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 ,

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

Code:

---

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

Can you give me xact code for package

I am new to oracle

Thanks for your help

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...:)

Code:

---

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)

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;

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


- Chris

updated_date is working

but updated_by value is coming null

Thanks

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

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

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

Are you ever calling the procedure to set the variable in that package??? As I said, you need to call it when the person logs in.

Also, why are you now grabbing the date as well, since you're not using it anywhere?

Also, please use the [ C O D E ] and [ / C O D E ] (without the spaces) markers around your code blocks so they're readable.

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;

Also, what is USER above?

You need to get this package working to set the g_CTUT_ID attribute correctly. Once you are sure of that, make sure it is being called in the app and set correctly. Once you are sure of that, make sure the triggers are working properly. This is simple debugging now.

- Chris

i JUST DID SOME DEBUGGING
SQL> CREATE OR REPLACE TRIGGER ORA_SYSTEM_USER_BIR_TR
2 BEFORE
3 INSERT
4 ON
5 ORA_SYSTEM_USER
6 FOR
7 EACH ROW
8 BEGIN
9 :NEW.CREATED_BY := GLOBAL_PACKAGE.G_CTUT_ID;
10 :NEW.CREATED_DATE := SYSDATE ;
11
12 DBMS_OUTPUT.PUT_LINE(:NEW.CREATED_BY);
13 DBMS_OUTPUT.PUT_LINE(:NEW.CREATED_DATE);
14
15 END ORA_SYSTEM_USER_BIR_TR;
16 /

Trigger created.

SQL>
SQL>
SQL> INSERT INTO ORA_SYSTEM_USER(CTUT_ID,CURRENT_USER_ID)
2 VALUES(6,'EXAMPLE6');
15-MAY-03

1 row created.


SEE IN THE ABOVE MESSAGE IT CAME ONLY CREATED_DATE NOT CREATED_BY15-MAY-03

*sigh*

Please read my previous post again.

- Are you even populating the package variable correctly?

- What is USER?

- Use the [ C O D E ] and [ / C O D E ] tags around any code you post

etc.

You didn't address a single issue in my previous post except to tell me 'I did some debugging'.

- Chris