CODEWITHSUNDEEP
databaseoracle-databaseplsql
LearningToCode
LearningToCode

Reputation: 390

Set Recursion level in Oracle db trigger

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

Upvotes: 1

Views: 198

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

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;

Upvotes: 2

Related Questions