Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-04092: cannot COMMIT in a trigger while creating seq inside a trigger

Khaja HussainOct 24 2009 — edited Oct 25 2009
Hi,

iam getting the ORA-04092: cannot COMMIT in a trigger while creating seq inside a trigger, kindly anyone explain why iam getting the error though i don't any commit statement in it.


CREATE OR REPLACE TRIGGER TRG_GEN_SEQUENCES
BEFORE INSERT on MASTER_TABLE
FOR EACH ROW
DECLARE
V_PROD VARCHAR2(5);
N_ID NUMBER := 0;
CT NUMBER := 0;
ERR_MSG VARCHAR2(2000);
BEGIN



-- Retrieve the ID e of the last inserted row which is 100 by default
-- set the default client_id value with nextvalue of sequence prod_IDS
IF :NEW.ID = 100 THEN
V_PROD := :NEW.PROD;
SELECT PROD_IDS.NEXTVAL INTO N_ID FROM DUAL;
:NEW.ID := N_ID;
END IF;

BEGIN

SELECT COUNT(*)
INTO CT
FROM USER_SEQUENCES US
WHERE UPPER(US.SEQUENCE_NAME) = UPPER(V_PROD);

IF CT = 0 THEN
-- create the sequence with name of V_PROD if doesn't exist

INSERT INTO CDR_SQL_ERR
(DB_OBJ, ERR_MSG, PROC_DATE)
VALUES
('TRG_GEN_SEQUENCES',
V_PROD || ' sequence will be created ', SYSDATE);

EXECUTE IMMEDIATE 'create sequence ' || V_PROD ||
' minvalue 1 maxvalue 999999 start with 1';

ELSE

INSERT INTO CDR_SQL_ERR
(DB_OBJ, ERR_MSG, PROC_DATE)
VALUES
('TRG_GEN_SEQUENCES',
V_PROD || ' sequence alreday exist',
SYSDATE);

END IF;

EXCEPTION
WHEN OTHERS THEN
ERR_MSG := TO_CHAR(SQLERRM) || ' ';
INSERT INTO SQL_ERR
(DB_OBJ, ERR_MSG, PROC_DATE)
VALUES
('TRG_GEN_SEQUENCES', ERR_MSG || SEQ_DDL, SYSDATE);
END;

EXCEPTION
WHEN OTHERS THEN
NULL;
ERR_MSG := TO_CHAR(SQLERRM) || ' ';
INSERT INTO SQL_ERR
(DB_OBJ, ERR_MSG, PROC_DATE)
VALUES
('TRG_GEN_SEQUENCES', ERR_MSG || SEQ_DDL, SYSDATE);
COMMIT;
END;

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 22 2009
Added on Oct 24 2009
16 comments
4,835 views