Monday, 14 November 2016

Automated Trigger Creation for Insert Operations on Tables

Select Table_Name, 
'CREATE or REPLACE TRIGGER TRG_'||Table_Name||' AFTER INSERT ON '||'MODELREPO.'||Table_NAME||
' FOR EACH ROW DECLARE BEGIN INSERT INTO LOCAL.'||Table_Name||' Values('||
Listagg(':new.'||Column_Name,',') within group (order by column_id) || '); END;'||chr(10) ||'/' TRG_Creation

from all_tab_columns where owner='LOCAL'
and table_name in (Select table_name from tablelist where num_rows>1)
group by Table_Name order by 1 


​ 



No comments:

Post a Comment