Re: Are Triggers Really that Bad or ....???

Thanks a lot guys for all the help.

I have writen a stored procedure which does this work as I needed to do some checks also. I have pasted the stored procedure at the bottom of the post. This stored procedure generates a file which has the statements to create the triggers.

Now my question is that after executing this procedure I have to go to go back to the sql prompt and issue another statement to excute this file. Is there a way where I can load this file from with a stored proceure and execte it. I know it can be done using the same method you use to issue O/S command. But what I am looking at is a way where I dont have to login again and issue these statements.

Basically where it just loads the trigger creation statements from the file and runs them from with the same session as the stored procedure

Thanks

This is what I have done

CODE

Create or replace procedure generate_compile_triggers (p_user in varchar2) as

cursor list_all_recs(V_USER IN VARCHAR2) is select table_name from sys.dba_tab_columns
where column_name = ('CREATED_DATE') and owner=V_user intersect
select table_name from sys.dba_tab_columns where column_name = ('UPDATED_DATE') and owner=V_user;

CURSOR list_updated_date_recs(V_USER IN VARCHAR2) IS select table_name from sys.dba_tab_columns
where column_name IN ('CREATED_DATE','UPDATED_DATE') and owner=V_USER MINUS
select table_name from sys.dba_tab_columns Where column_name = ('CREATED_DATE') and owner=V_USER;

CURSOR list_created_date_recs(V_USER IN VARCHAR2) IS select table_name from sys.dba_tab_columns
where column_name IN ('CREATED_DATE','UPDATED_DATE') and owner=V_USER MINUS
select table_name from sys.dba_tab_columns Where column_name = ('UPDATED_DATE') and owner=V_USER;

 l_output utl_file.file_type;
 trig_definition varchar2(32000);
 V_USER VARCHAR2(30); begin

 l_output := utl_file.fopen( 'c:\', 'generateCompile.sql', 'w', 32000 );  v_user := upper(p_user);

 for i in list_all_recs(V_USER) loop

  trig_definition := 'Create or replace Trigger '||v_USER||'.'||i.table_name||'_Trg'||chr(10)||

' BEFORE INSERT OR UPDATE ON '||v_USER||'.'||i.table_name||chr(10)||
' for each row'||chr(10)||
' BEGIN'||chr(10)||
' IF inserting THEN'||chr(10)||
' :NEW.created_date:=sysdate;'||chr(10)||
' :NEW.created_by:=user;'||chr(10)||
' ELSE'||chr(10)||
' :NEW.updated_date:=sysdate;'||chr(10)||
' :NEW.updated_by:=user;'||chr(10)||
' END IF;'||chr(10)||
'END;'||chr(10)||
'/';

     utl_file.put( l_output,trig_definition );
     utl_file.new_line(l_output);

 end loop;

 for i in list_updated_date_recs(V_USER) loop

  trig_definition := 'Create or replace Trigger '||v_USER||'.'||i.table_name||'_Trg'||chr(10)||

' BEFORE UPDATE ON '||v_USER||'.'||i.table_name||chr(10)||
' for each row'||chr(10)||
' BEGIN'||chr(10)||
' IF Updating THEN'||chr(10)||
' :NEW.updated_date:=sysdate;'||chr(10)||
' :NEW.updated_by:=user;'||chr(10)||
' END IF;'||chr(10)||
'END;'||chr(10)||
'/';

     utl_file.put( l_output,trig_definition );
     utl_file.new_line(l_output);

 end loop;

 for i in list_created_date_recs(V_USER) loop

  trig_definition := 'Create or replace Trigger '||v_USER||'.'||i.table_name||'_Trg'||chr(10)||

' BEFORE INSERT ON '||v_USER||'.'||i.table_name||chr(10)||
' for each row'||chr(10)||
' BEGIN'||chr(10)||
' IF inserting THEN'||chr(10)||
' :NEW.created_date:=sysdate;'||chr(10)||
' :NEW.created_by:=user;'||chr(10)||
' END IF;'||chr(10)||
'END;'||chr(10)||
'/';

     utl_file.put( l_output,trig_definition );
     utl_file.new_line(l_output);

 end loop;

 utl_file.fclose( l_output );

end;
/ Received on Thu Aug 22 2002 - 11:58:05 CDT