Set Recursion level in Oracle db trigger

Question

I am working on an pl/sql use case where I need to create a trigger for the "Create" and inside the trigger it again Creates a table. Is there a way I can set the recursion level to 1 similar to sql-server in Oracle db. Please suggest

Edit:

Adding the trigger and the create table command

CREATE TRIGGER abc
AFTER CREATE ON DATABASE
BEGIN
IF TRIGGER_NESTLEVEL() <= 1 THEN
EXECUTE IMMEDIATE
'CREATE TABLE dep (
             dep_id NUMBER(4) NOT NULL,
             dep_nm VARCHAR2(30),
             c_id NUMBER(4) NOT NULL)
       PARTITION BY HASH(dep_id)
       (PARTITION t1 tablespace tbs2)';
EXECUTE IMMEDIATE
'CREATE TABLE dep_temp AS SELECT * from dep where 1=2';
EXECUTE IMMEDIATE
'ALTER TABLE dep
    EXCHANGE PARTITION t1
    WITH TABLE dep_temp
    WITHOUT VALIDATION
    UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE
'create table t1_temp for exchange with table dep';
EXECUTE IMMEDIATE
'ALTER TABLE dep
EXCHANGE PARTITION t1
WITH TABLE t1_temp
WITHOUT VALIDATION';
END IF;
END;
/

CREATE TABLE dep3(
             dep_id NUMBER(4) NOT NULL,
             dep_nm VARCHAR2(30),
             c_id NUMBER(4) NOT NULL);

Using "Execute Immediate" I can create table inside a trigger. — LearningToCode
– LearningToCode, Commented Jul 6, 2020 at 21:05
Why do you want to do this? Seems overly complicated. What business goal are you trying to accomplish? — OldProgrammer
– OldProgrammer, Commented Jul 6, 2020 at 21:25
@OldProgrammer I have a set of steps to follow. Have just converted them to pl/sql statements. — LearningToCode
– LearningToCode, Commented Jul 6, 2020 at 22:37

Connor McDonald · Accepted Answer · 2020-07-07 00:37:02Z

2

It does seem an interesting use case for triggers, because I'd be "concerned" to have objects just appearing out of the blue in the database. Normally DDL we want to be fairly carefully monitored on a production system. Anyway, an option could be a package variable that you set, eg

CREATE TRIGGER abc
AFTER CREATE ON DATABASE
BEGIN
  if not pkg.i_am_already_in_the_trigger then
    pkg.i_am_already_in_the_trigger := true;
    EXECUTE IMMEDIATE ....
    pkg.i_am_already_in_the_trigger := false;
  end if;
EXCEPTION WHEN OTHERS THEN
  pkg.i_am_already_in_the_trigger := false;
  raise;
END;

answered Jul 7, 2020 at 0:37

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set Recursion level in Oracle db trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related