Hi,
I would like to create a view as soon as table is created. I created following trigger. Here I am creating a table "ttt", once that is created, program will create a view for dwuser from the current user. when i run the execute immediate statements, it runs fine. but when i put it in trigger, it does not create a view. I am using combination of trigger and dbms_job.
CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON slbdw.SCHEMA DECLARE
v_sql VARCHAR2(2000);
v_cnt NUMBER := 1;
v_current_user VARCHAR2(100);
l_str1 VARCHAR2(4000);
l_str2 VARCHAR2(2000);
l_job1 NUMBER;
l_job2 NUMBER;
BEGIN
IF sys.dictionary_obj_type = 'TABLE' AND sys.dictionary_obj_name = 'TTT' THEN
l_str1 := ' BEGIN execute immediate "grant select on '
|| ora_dict_obj_owner
|| '.'
|| ora_dict_obj_name
|| ' to dwuser";
execute immediate "Create or replace view DWUSER.'
|| sys.dictionary_obj_name
|| ' as select * from '
|| ora_dict_obj_owner
|| '.'
|| sys.dictionary_obj_name
|| '"; END;';
dbms_job.submit(l_job1, replace(l_str1, '"', ''''), current_timestamp + INTERVAL
'5' SECOND);
END IF;
END;
/
create table ttt as select 1 from dual;
please advice if I am making any mistake or does the trigger not allow creation of view via dbms_job??
thanks
kumar