Create a Sequence, through a trigger. Oracle

1

I'm trying to create a sequence in a trigger .

When trying to create by Execute Immediate , I'm having the following return:

  

ORA: 4092 - Can not commit in a trigger.

For all I've ever researched, using Execute Immediate in a trigger is not possible, since it is in dll which issues an automatic commit .

I would like to know if there is any other way to create a sequence by a trigger.

Follow the trigger code

CREATE OR REPLACE TRIGGER "TR_TEST"  
  AFTER UPDATE ON TEST_TABLE
  FOR EACH ROW
DECLARE
BEGIN
   BEGIN
     EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ01' ||
                       '      MINVALUE 1' ||
                       '      MAXVALUE 999999999999999999999999999' ||
                       '      START WITH 1' ||
                       '      INCREMENT BY 1' ||
                       '      NOCACHE';      
   EXCEPTION
       WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20200, 'Error');
   END;  
END 
    
asked by anonymous 16.04.2018 / 21:07

2 answers

0

A Trigger should not create an object. Each time it is fired, it will try to create the object again ... From what I understand, you want to increment the sequence SEQ01 after the update of the TEST_TABLE table. Right? If so, within immediate execute it should contain:

select SEQ01.nextval from dual;

Since you created the sequence previously.

    
17.04.2018 / 15:18
0

You can create the sequence in a procedure and call the procedure inside your trigger so you can create or use the sequence.

Take a look at this link: link

But I believe that the best practice would be to create the normal sequence because you already know that it should exist.

    
17.04.2018 / 15:21