CREATE OR REPLACE PROCEDURE CREATE_AUDIT_TRIGGER (v_table_name IN VARCHAR2,		    -- Table Name.
	   	  		  							   	  v_enabled    IN VARCHAR2 DEFAULT 'Y') -- Enable Trigger on creation?												 
IS
/*    
   DESCRIPTION:
   This procedure creates a standard audit trigger for a standard audit table.
   
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        19/06/2002  DRC       	   1. Created this procedure.

   PARAMETERS:
   NOTES:
   Make sure owner has CREATE ANY TRIGGER & CREATE ANY SEQUENCE privilege.
*/

  sql_string 				VARCHAR2(4000);
  sql_ddl_string			VARCHAR2(4000); -- Added due to bug Doc ID: 104059.1
  v_column_name 			VARCHAR2(30);
  v_audit_table_name 		VARCHAR2(30) DEFAULT v_table_name || '_AUDITS';
  v_transaction_id_seq_name VARCHAR2(30) DEFAULT v_table_name || '_AUD_SEQ';
  v_trigger_name 			VARCHAR2(30) DEFAULT v_table_name || '_TRIG';
  v_seq_created        		VARCHAR2(1);
  
 CURSOR get_table_info IS
 select column_name
 from user_tab_columns
 where table_name = v_table_name
 order by column_id;
   
 CURSOR get_audit_table_info IS
 select column_name
 from user_tab_columns
 where table_name = v_audit_table_name
 order by column_id;
 
BEGIN
	 
	-- Create sequence for Transaction ID first.
	BEGIN
	
		 SELECT 'X' 
		 INTO v_seq_created 
		 FROM USER_SEQUENCES
		 WHERE SEQUENCE_NAME = v_transaction_id_seq_name;
			 
		 EXCEPTION
		 WHEN no_data_found THEN
		 	  
			  sql_string := 'CREATE SEQUENCE ' || v_transaction_id_seq_name || ' START WITH 0 NOMAXVALUE NOCYCLE NOCACHE MINVALUE 0';
	
			  sql_ddl_string := sql_string;
			   
			  execute immediate sql_ddl_string;
			 
		 WHEN others THEN
		 	  dbms_output.put_line('Error creating sequence. '|| sqlerrm); 
	END;	 
	
	sql_string := 'CREATE OR REPLACE TRIGGER ' || v_trigger_name || chr(10) ||
			   	  ' BEFORE INSERT or DELETE or UPDATE' || chr(10) ||
				  ' ON ' || v_table_name || chr(10) ||
 				  ' FOR EACH ROW' || chr(10) ||
				  ' DECLARE' || chr(10) ||
				  ' trans_id number;' || chr(10) ||
				  ' trans_type varchar2(6);' || chr(10) ||
				  ' today date;' || chr(10) ||
				  ' begin' || chr(10) ||
				  '	select sysdate into today from dual;' || chr(10) ||
				  ' if INSERTING then' || chr(10) ||
				  '	 trans_type := ''INSERT'';' || chr(10) ||
				  '	elsif DELETING then' || chr(10) ||
				  '	 trans_type := ''DELETE'';' || chr(10) ||
				  '	elsif UPDATING then' || chr(10) ||
				  '	 trans_type := ''UPDATE'';' || chr(10) ||
				  ' end if;' || chr(10) ||
				  ' select ' || v_transaction_id_seq_name || '.nextval into trans_id from dual;' || chr(10) ||
				  ' insert into ' || v_audit_table_name || ' values( trans_id,' || chr(10); 
				  
	--Now for the value columns
	OPEN get_table_info;
	<<code_loop3>>
	LOOP
		
		v_column_name := NULL;
		
		FETCH get_table_info INTO v_column_name;
		
		IF get_table_info%NOTFOUND THEN
         		EXIT code_loop3;
    	END IF;
		
		sql_string := sql_string || ' :new.' || v_column_name || ',' || chr(10);
		
	END LOOP;
	CLOSE get_table_info;
	
	
	--Now for the value columns
	OPEN get_table_info;
	<<code_loop2>>
	LOOP
		
		v_column_name := NULL;
		
		FETCH get_table_info INTO v_column_name;
		
		IF get_table_info%NOTFOUND THEN
         		EXIT code_loop2;
    	END IF;
		
		sql_string := sql_string || ' :old.' || v_column_name || ',' || chr(10);
		
	END LOOP;
	CLOSE get_table_info;
	
	sql_string := sql_string || ' user, trans_type, sysdate); ' || 'end;';
	
	sql_ddl_string := sql_string;
	
	execute immediate sql_ddl_string;
	
	dbms_output.put_line('Audit Table Trigger ' || v_trigger_name || ' Created.');

	
	-- Enable or Disable Trigger
	
	IF v_enabled = 'Y' THEN
	
	   sql_string := 'ALTER TRIGGER ' || v_trigger_name || ' ENABLE';
	
	ELSE
	 
	   sql_string := 'ALTER TRIGGER ' || v_trigger_name || ' DISABLE';
	 
	END IF;
	
	sql_ddl_string := sql_string;
	
	execute immediate sql_ddl_string;
	
	dbms_output.put_line('Audit Table Trigger ' || v_trigger_name || ' Created.' || v_enabled );

	EXCEPTION
	WHEN others THEN
		 dbms_output.put_line(sqlerrm);	 	 
	

END;
/