How do I write generic code in triggers?

I would like to write a generic logging routine in an after_update trigger. This routine should write a logging message for every updated attribute of a table. The snag is that I do not want to code the names of these attributes but determine them run-time.

The following code example determines what attributes there are in the table scott.emp. Within the loop the old and new values of each attribute should be compared and when they differ, a logging message should stored.

TRIGGER "AFTER_UPDATE_EMP" AFTER UPDATE ON SCOTT.EMP FOR EACH ROW
DECLARE         ignore INTEGER;

        select_cursor    INTEGER;
        column_name      VARCHAR2(30);
        data_type        VARCHAR2(9);
        column_id        NUMBER;

        name_of_column_old      VARCHAR2(30);
        name_of_column_new      VARCHAR2(30);

BEGIN

• prepare a cursor and columns to select from the
  • all_tab_columns table the names and the data types of
  • all attributes of the currently handled table

    ---

    select_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE( select_cursor, 'SELECT column_name, data_type, column_id FROM all_tab_columns WHERE table_name = ''EMP''', DBMS_SQL.V7); ignore := DBMS_SQL.LAST_ERROR_POSITION;

        DBMS_SQL.DEFINE_COLUMN(select_cursor, 1, column_name, 30);
        DBMS_SQL.DEFINE_COLUMN(select_cursor, 2, data_type, 9);
        DBMS_SQL.DEFINE_COLUMN(select_cursor, 3, column_id);
        ignore  := DBMS_SQL.EXECUTE(select_cursor);


        ----------------------------------------------------
        -- select from the all_tab_columns table the names and the 


	-- data types of all attributes of the currently handled 

	-- table and for each table attribute check if 

	-- new != old and if so, store a logging message

        ----------------------------------------------
        LOOP
                IF (DBMS_SQL.FETCH_ROWS(select_cursor)>0) THEN

                        -- get the attribute name, data type
                        DBMS_SQL.COLUMN_VALUE(	select_cursor, 
						1, 
						column_name);
                        DBMS_SQL.COLUMN_VALUE(	select_cursor,
						2, 
						data_type);
                        DBMS_SQL.COLUMN_VALUE(	select_cursor, 
						3, 
						column_id);     

                        name_of_column_new := ':old.' || column_name;
                        name_of_column_new := ':new.' || column_name;
                              
--                      !!!So far everythings oke but here the problem
--			!!!begins
--
--                      I would now like to do something like:
--
--                      IF      the value of the attribute as named in
--				the variable name_of_column_new
--				!=
--				the value of the attribute as named in
--				the variable name_of_column_old
--                      THEN    Store_logging_message(
--					column_name, 
--                                      the value of the attribute as
--					named in the variable
--					name_of_column_new,
--                                      the value of the attribute as
--					named in the variable
--					name_of_column_new);
--                      END IF;
--			!!! But how?
                    
                ELSE
                        -- all attributes handled
                        EXIT;
                END IF;
        END LOOP;

        DBMS_SQL.CLOSE_CURSOR(select_cursor);

END; Received on Fri Jan 09 1998 - 00:00:00 CST