Skip to Main Content
  • Questions
  • DDL Trigger to mimic same changes to other tables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, tauseef.

Asked: April 07, 2017 - 3:02 pm UTC

Last updated: April 15, 2017 - 4:26 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I was trying to write a DDL trigger that will apply the same DDL to other tables when 1 particular table is bieng altered.

That trigger is working fine when I add column without any NOT NULL constraint on column but gives error when try to add column with NOT NULL.

Please see the example:

create table tab_a (id number);
create table tab_b as select * from tab_a;
create table tab_c as select * from tab_a;
create global temporary table tab_d as select * from tab_a;


CREATE OR REPLACE TRIGGER TRG_SYNC_WITH_REG_TD
AFTER ALTER ON SCHEMA

DECLARE
  v_object_name   user_objects.object_name%TYPE; 
  v_object_type   user_objects.object_type%TYPE; 
  v_operation     varchar2(30); 
  sql_text        ora_name_list_t;
  v_sqlStr        CLOB;  
  i               PLS_INTEGER; 
BEGIN
  SELECT ora_dict_obj_name,ora_dict_obj_type, ora_sysevent
  INTO v_object_name,v_object_type,v_operation 
  FROM DUAL;
  
  i := sql_txt(sql_text);
  FOR indx IN sql_text.FIRST..sql_text.LAST LOOP
    v_sqlStr :=v_sqlStr||sql_text(indx);
  END LOOP;
     
  IF 
    (
     v_object_name = 'TAB_A' AND v_object_type = 'TABLE'  AND v_operation = 'ALTER' AND 
     UPPER(SUBSTR(SQL_TEXT(1),INSTR(SQL_TEXT(1),' ',1,3)+1,3)) IN ('ADD','DROP') 
    )
  THEN
    dbms_output.put_line(REPLACE(UPPER(v_sqlStr),'TAB_A','TAB_B'));
    Execute Immediate REPLACE(UPPER(v_sqlStr),'TAB_A','TAB_B');
    Execute Immediate REPLACE(UPPER(v_sqlStr),'TAB_A','TAB_C');

  ELSIF 
    (
     v_object_name = 'TAB_C' AND v_object_type = 'TABLE'  AND v_operation = 'ALTER' AND 
     UPPER(SUBSTR(SQL_TEXT(1),INSTR(SQL_TEXT(1),' ',1,3)+1,3)) IN ('ADD','DROP') 
    )
  THEN
  
    Execute Immediate REPLACE(UPPER(v_sqlStr),'TAB_C','TAB_D');
  
  END IF;
END TRG_SYNC_WITH_REG_TD;
/

alter table tab_a add (name varchar2(10),
name1 varchar2(10) not null,
name2 varchar2(10),
name3 varchar2(10),
name4 varchar2(10));

and Connor said...

Can you elaborate ? I'm not seeing any problems


SQL>
SQL> create table tab_a (id number);

Table created.

SQL> create table tab_b as select * from tab_a;

Table created.

SQL> create table tab_c as select * from tab_a;

Table created.

SQL> create global temporary table tab_d as select * from tab_a;

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_SYNC_WITH_REG_TD
  2  AFTER ALTER ON SCHEMA
  3
  4  DECLARE
  5    v_object_name   user_objects.object_name%TYPE;
  6    v_object_type   user_objects.object_type%TYPE;
  7    v_operation     varchar2(30);
  8    sql_text        ora_name_list_t;
  9    v_sqlStr        CLOB;
 10    i               PLS_INTEGER;
 11  BEGIN
 12    SELECT ora_dict_obj_name,ora_dict_obj_type, ora_sysevent
 13    INTO v_object_name,v_object_type,v_operation
 14    FROM DUAL;
 15
 16    i := sql_txt(sql_text);
 17    FOR indx IN sql_text.FIRST..sql_text.LAST LOOP
 18      v_sqlStr :=v_sqlStr||sql_text(indx);
 19    END LOOP;
 20
 21    IF
 22      (
 23       v_object_name = 'TAB_A' AND v_object_type = 'TABLE'  AND v_operation = 'ALTER' AND
 24       UPPER(SUBSTR(SQL_TEXT(1),INSTR(SQL_TEXT(1),' ',1,3)+1,3)) IN ('ADD','DROP')
 25      )
 26    THEN
 27      dbms_output.put_line(REPLACE(UPPER(v_sqlStr),'TAB_A','TAB_B'));
 28      Execute Immediate REPLACE(UPPER(v_sqlStr),'TAB_A','TAB_B');
 29      Execute Immediate REPLACE(UPPER(v_sqlStr),'TAB_A','TAB_C');
 30
 31    ELSIF
 32      (
 33       v_object_name = 'TAB_C' AND v_object_type = 'TABLE'  AND v_operation = 'ALTER' AND
 34       UPPER(SUBSTR(SQL_TEXT(1),INSTR(SQL_TEXT(1),' ',1,3)+1,3)) IN ('ADD','DROP')
 35      )
 36    THEN
 37
 38      dbms_output.put_line(REPLACE(UPPER(v_sqlStr),'TAB_C','TAB_D'));
 39      Execute Immediate REPLACE(UPPER(v_sqlStr),'TAB_C','TAB_D');
 40
 41    END IF;
 42  END TRG_SYNC_WITH_REG_TD;
 43  /

Trigger created.

SQL>
SQL> set serverout on
SQL>
SQL> alter table tab_a add (name varchar2(10),
  2    name1 varchar2(10) not null,
  3    name2 varchar2(10),
  4    name3 varchar2(10),
  5    name4 varchar2(10));
ALTER TABLE TAB_B ADD (NAME VARCHAR2(10),
  NAME1 VARCHAR2(10) NOT NULL,
  NAME2 VARCHAR2(10),
  NAME3 VARCHAR2(10),
  NAME4
VARCHAR2(10))
ALTER TABLE TAB_D ADD (NAME VARCHAR2(10),
  NAME1 VARCHAR2(10) NOT NULL,
  NAME2 VARCHAR2(10),
  NAME3 VARCHAR2(10),
  NAME4
VARCHAR2(10))

Table altered.

SQL>
SQL>   desc tab_a
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(10)
 NAME1                                                                   NOT NULL VARCHAR2(10)
 NAME2                                                                            VARCHAR2(10)
 NAME3                                                                            VARCHAR2(10)
 NAME4                                                                            VARCHAR2(10)

SQL> desc tab_b
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(10)
 NAME1                                                                   NOT NULL VARCHAR2(10)
 NAME2                                                                            VARCHAR2(10)
 NAME3                                                                            VARCHAR2(10)
 NAME4                                                                            VARCHAR2(10)

SQL> desc tab_c
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(10)
 NAME1                                                                   NOT NULL VARCHAR2(10)
 NAME2                                                                            VARCHAR2(10)
 NAME3                                                                            VARCHAR2(10)
 NAME4                                                                            VARCHAR2(10)

SQL> desc tab_d
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(10)
 NAME1                                                                   NOT NULL VARCHAR2(10)
 NAME2                                                                            VARCHAR2(10)
 NAME3                                                                            VARCHAR2(10)
 NAME4                                                                            VARCHAR2(10)

SQL>
SQL>



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here