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