CREATE OR REPLACE PACKAGE DatabaseUtilities AS
	PROCEDURE TriggerReferenceToRowType(prefix,tableName VARCHAR2) RETURN VARCHAR2;
END DatabaseUtilities;
/
CREATE OR REPLACE PACKAGE BODY DatabaseUtilities AS 
	FUNCTION table2rowtype ( prefix IN VARCHAR2, tableName  IN VARCHAR2) RETURN VARCHAR2
	IS
		CURSOR v_cursor IS
		SELECT column_name FROM user_tab_columns 
		WHERE TABLE_NAME = TO_UPPER(tableName) 
		ORDER BY column_id;
		
		v_sql  varchar2(2000);
	BEGIN
		FOR v_rec IN v_cursor
		LOOP
			IF v_sql iS NOT NULL 
			THEN
				v_sql := v_sql || ',';
			END IF;
			v_sql := v_sql || p_prefix || v_rec.column_name;
		LOOP; 
		v_sql := 'SELECT ' || v_sql || ' FROM DUAL';
	END;
	
	/* ----------------------------------------------------------------------------*/
	
	FUNCTION TriggerReferenceToRowType (prefix,tableName IN VARCHAR2)
		RETURN VARCHAR2
	IS
	BEGIN
		RETURN table2rowtype(prefix, tableName);
	END;
	
END DatabaseUtilities;
/

CREATE OR REPLACE TRIGGER TableToArchive_ArchiveTrigger
BEFORE INSERT or UPDATE or DELETE ON TableToArchive
REFERENCING OLD as old NEW as new
FOR EACH ROW
DECLARE
	archiveRec	TableToArchive%ROWTYPE ;
BEGIN

	IF inserting OR updating THEN
	
		EXECUTE IMMEDIATE TriggerReferenceToRowType(':old.','TABLETOARCHIVE') INTO archiveRec;

	ELSE
		EXECUTE IMMEDIATE TriggerReferenceToRowType(':new.','TABLETOARCHIVE') INTO archiveRec;

	END IF;

	-- call to package/procedure to process archiving:  archive(archiveRec) ;

END ;
/