------------------------------------------------------------------
prompt Trigger: sdd_ort_as
--  Notes: Tidies up tables skipped by row level triggers because
--         of mutating table errors
------------------------------------------------------------------
CREATE OR REPLACE TRIGGER SDD_ORT_AS
 after delete on I$SDD_ORT
declare
 cursor tabs_for_delete is
  select ob.object_ivid par_ivid, tab.table_name tab_name, tab.irid tab_irid 
    from I$SDD_DELETED_OBJECTS ob
        ,RM_SQL_TABLES tab
   where tab.irid=ob.table_irid;
 csr  pls_integer;
 exec pls_integer;
 stmt varchar2(200);
begin
 --Execute a loop to delete from any child tables that could not
 --be deleted in row level triggers because of mutating table 
 --errors (B1186898)
 csr:=dbms_sql.open_cursor; 
 for del in tabs_for_delete loop
  stmt:='delete I$'||del.tab_name||
        ' where parent_ivid=:par_ivid'; 		
  dbms_sql.parse(csr,stmt,DBMS_SQL.NATIVE);
  dbms_sql.bind_variable(csr,':par_ivid',del.par_ivid);
  exec:=dbms_sql.execute(csr);
 end loop;
 dbms_sql.close_cursor(csr);
 delete from I$SDD_DELETED_OBJECTS;
exception
 when others then
  --Ensure cursor gets closed
  if dbms_sql.is_open(csr) then
   dbms_sql.close_cursor(csr);
  end if;
end;   
/

