Forum Stats

  • 3,838,243 Users
  • 2,262,343 Discussions
  • 7,900,552 Comments

Discussions

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

Khaja Hussain
Khaja Hussain Member Posts: 9 Blue Ribbon
edited Oct 25, 2009 5:29AM in 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;
Tagged:
«1

Answers

  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited Oct 24, 2009 5:32AM
    Hi,

    DDL has two implicit commits, one before and one after the statement.

    But honestly, why don't you just create the sequences you need. If this is because you want sooo many sequences, then perhaps you could use the data dictionary to create a script.
    You shouldn't be writing a trigger for this.

    Btw, you do have an explicit commit, in your exception handler.

    Regards
    Peter
  • 728534
    728534 Member Posts: 1,386
    I am just giving you a way to achieve what you are doing.
    I do not know if what you are doing is correct or not. Iw ill pressume you need the trigger.. here is what you need to do

    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);

    PRAGMA AUTONOMOUS_TRANSACTION; -- ADD THIS AND IT WILL WORK FINE
    BEGIN
    /*rest of you code*/
    end;
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Buga wrote:
    I am just giving you a way to achieve what you are doing.
    ...
    PRAGMA AUTONOMOUS_TRANSACTION; -- ADD THIS AND IT WILL WORK FINE
    Ouch!

    Please do not use this. This will split up your transactions. And when one of them fails, the other ones might succeed and you'll have inconsistent data.

    Regards,
    Rob.
  • 728534
    728534 Member Posts: 1,386
    Thanks ROB.
    But i beleive he is commting only in WHEN OTHERS (Another Bug if you may say)
    So i dont think it will split up the transaction (Commiting only in exception block)

    Cheers!!!
    Bhushan
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    What business goal are you trying to achieve here?
    The idea that I can deduce from your code doesn't sound good. If, for some reason, you only want 1 sequence, then this isn't the way to do it. Just use one_seq.nextval inside your insert statement. To remove possible contention, one-sequence-per-table is a much safer bet.

    Regards,
    Rob.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Brushan,

    A transaction involves more than a single insert statement and one execution of this database trigger. What if an after-row-insert trigger fails? The code in the before-row-insert trigger has committed ... What if the insert succeeds and the user issues a rollback? The code in the before-row-insert-trigger has committed ...

    There is also an implicit commit in his "execute immediate 'create sequence'" statement, by the way.

    Regards,
    Rob.
  • 728534
    728534 Member Posts: 1,386
    Dear Rob,
    I beleive autonomous transaction will only commit the uncommited data in that trigger and not the one which are outside the BLOCK of CODE i.e any UNCOMMITED DATA FROM the application or any other TRIGGER.

    If what you are saying is correct than One can never have an audit trigger.

    Regards,
    Bhushan
  • Khaja Hussain
    Khaja Hussain Member Posts: 9 Blue Ribbon
    Hi Everyone,

    My intention is to create a sequence at runtime(using dynamic SQL), i don't know the name of the sequence in advance. It will not create the sequence if it exists, once the sequence is created it will be rest after end of every month.

    Removing the commit statement from when others block didn't solve the problem.
    Inclusion of PRAGMA AUTONOMOUS_TRANSACTION has thrown ORA-06519: active autonomous transaction detected and rolled back.

    Thanks

    Regards
    Khaja
  • Khaja Hussain
    Khaja Hussain Member Posts: 9 Blue Ribbon
    Hi Everyone,

    My intention is to create a sequence at runtime(using dynamic SQL), i don't know the name of the sequence in advance. It will not create the sequence if it exists, once the sequence is created it will be rest after end of every month.

    Removing the commit statement from when others block didn't solve the problem.
    Inclusion of PRAGMA AUTONOMOUS_TRANSACTION has thrown ORA-06519: active autonomous transaction detected and rolled back.

    Thanks

    Regards
    Khaja
  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    My intention is to create a sequence at runtime(using dynamic SQL)
    As people are trying to tell you, this is a pretty damn good sign that you're taking a wrong approach for some reason.
    Why do you think you need to dynamically create sequences?
This discussion has been closed.