SQL & PL/SQL

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;