Create table if it does not exist in the database with an ORACLE SQL trigger

Question

Create table if it does not exist in the database with an ORACLE SQL trigger

Navigation

#1 by (0 votes)

0

As the statement says, I need a trigger that triggers after inserting a record in a table "table0". If the table1 does not exist this trigger must create it and then perform the insert. I had something like this:

CREATE OR REPLACE TRIGGER TRG_INSERT_HISTORIAL
AFTER INSERT ON tabla0
BEGIN
IF INSERTING THEN
  IF NOT EXISTS (tabla1)  THEN
    CREATE TABLE tabla1 (...);      

ELSE
   INSERT INTO TABLA1 VALUES (...);
END IF;
END;

But I do not know well the sentence of "IF THERE IS NO TABLE1" then create it and insert "data" to said table. I hope a little help, thanks!

oracle

asked by DregonBlade99 20.06.2018 в 22:54

source

1 answer

How to restart a python program in raspberry automatically? Help with an initial filter SuiteCRM

score 0 · Answer 1

To do what you need, you need to use DYNAMIC SQL inside PL / SQL. I do not know if it is recommended, but definitely, if you want to do it, you can do it:

BEGIN
  EXECUTE IMMEDIATE 'create table foo (a char(1))';
END;
/

Specifically, what you want to do could be something like this:

CREATE OR REPLACE TRIGGER TRG_INSERT_HISTORIAL
AFTER INSERT ON tabla0
DECLARE
   v_Cnt number;
BEGIN
  IF INSERTING THEN
    select count(1) into v_Cnt
      from user_tables
     where tablename = 'tabla1';
    if v_Cnt = 0 then -- la tabla no existe
       execute immediate 'CREATE TABLE tabla1 (a char(1), b date, c number)';
    end if;
    execute immediate 'insert into tabla1 values (''a'', sysdate, 1)';
  END;
END;