Help on Trigger

[orakle]

Hi ALL,

I am trying to create a package and create a trigger for solving the mutating error

But everything creates fine for me.

But when inserting it is giving error anyhelp please

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

SQL> select * from ora_system_user;

CTUT_ID CURRENT_USER_ID CREATED_BY CREATED_D UPDATED_BY UPDATED_D
--------- ------------------------------ ---------- --------- ---------- ---------
2 SCOTT 1 13-MAY-03
3 WKERN 1 13-MAY-03
1 KKISHORE 1 13-MAY-03

Package

create or replace package orasystemuser as
TYPE t_created_by is TABLE OF ora_system_user.CREATED_BY%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_created_DATE is TABLE OF ora_system_user.CREATED_DATE%TYPE
INDEX BY BINARY_INTEGER;
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;

create or replace package orasystemuser as
TYPE t_created_by is TABLE OF ora_system_user.CREATED_BY%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_created_DATE is TABLE OF ora_system_user.CREATED_DATE%TYPE
INDEX BY BINARY_INTEGER;
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;

V_SYSTEMCREATEDBY T_CREATED_BY;
V_SYSTEMCREATEDDATE T_CREATED_DATE;
V_SYSTEMUPDATEDBY T_UPDATED_BY;
V_SYSTEMUPDATEDDATE T_UPDATED_DATE;
V_SYSTEMCTUTID T_CTUT_ID;
V_NUMENTRIES BINARY_INTEGER := 0;
END ORASYSTEMUSER;

Trigger1:
CREATE OR REPLACE TRIGGER RLIMITSYSTEMUSER
BEFORE INSERT OR UPDATE ON ORA_SYSTEM_USER
FOR EACH ROW
BEGIN
ORASYSTEMUSER.V_NUMENTRIES := ORASYSTEMUSER.V_NUMENTRIES + 1;
ORASYSTEMUSER.V_SYSTEMCREATEDBY(ORASYSTEMUSER.V_NUMENTRIES) := :NEW.CREATED_BY;
ORASYSTEMUSER.V_SYSTEMCREATEDDATE(ORSSYSTEMUSER.V_NUMENTRIES) := :NEW.CREATED_DATE;
ORASYSTEMUSER.V_SYSTEMUPDATEDDATE(ORSSYSTEMUSER.V_NUMENTRIES) := :NEW.UPDATED_DATE;
ORASYSTEMUSER.V_SYSTEMUPDATEDBY(ORSSYSTEMUSER.V_NUMENTRIES) := :NEW.UPDATED_BY;
ORASYSTEMUSER.V_SYSTEMCTUTID(ORSSYSTEMUSER.V_NUMENTRIES) := :NEW.CTUT_ID;
END RLIMITSYSTEMUSER;

Trigger 2:
CREATE OR REPLACE TRIGGER TI_SYSTEMUSER
AFTER INSERT ON ORA_SYSTEM_USER
DECLARE
V_ID NUMBER;
V_CTUTID ORA_SYSTEM_USER.CTUT_ID%TYPE;
V_CREATEDBY ORA_SYSTEM_USER.CREATED_BY%TYPE;
V_CREATEDDATE ORA_SYSTEM_USER.CREATED_DATE%TYPE;
Cudate date;
Cuby number;
BEGIN
FOR V_LOOPINDEX IN 1..ORASYSTEMUSER.V_NUMENTRIES LOOP
V_CTUTID := ORASYSTEMUSER.V_SYSTEMCTUTID(V_LOOPINDEX);
V_CREATEDBY := ORASYSTEMUSER.V_SYSTEMCREATEDBY(V_LOOPINDEX);
V_CREATEDDATE := ORASYSTEMUSER.V_SYSTEMCREATEDDATE(V_LOOPINDEX);
SELECT COUNT(*) INTO V_ID
FROM ORA_SYSTEM_USER
WHERE CURRENT_USER_ID = USER;
IF V_ID > 0 THEN
SELECT CTUT_ID,SYSDATE INTO CUBY,CUDATE
FROM ORA_SYSTEM_USER
WHERE CURRENT_USER_ID = USER;
DBMS_OUTPUT.PUT_LINE(CUDATE);
Update ORA_system_user set created_by=cuby,created_date=cudate where CTUT_id= V_CTUTID;
ELSE
RAISE_APPLICATION_ERROR(-20000,'NO USER FOUND IN TABLE');
END IF;
END LOOP;
END TI_SYSTEMUSER;

SQL> INSERT INTO ORA_SYSTEM_USER
2 (CTUT_ID,CURRENT_USER_ID)
3 VALUES(4,'EXAMPLE');
INSERT INTO ORA_SYSTEM_USER
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORA_SYSTEM_USER"."CREATED_BY")

any help please

[marist89]

Originally posted by orakle

ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORA_SYSTEM_USER"."CREATED_BY")

You're trying to insert a null value into a not-null column.

[jammula]

Yah but in after insert trigger i am updating values