RE:How do I write generic code in triggers?

This is very interesting...
Instead of coding the procedure code directly into trigger body, try storing it as stored procedure and pass input parameters to the procedure from the trigger.
I have a feeling that triggers are not pre-compiled but stored procedures are.

Let me know if this helped...or anyway.. Oracle DBA/Support
Vinay Joshi
Pinnacle Insurance Plc

> -----Original Message-----
> From: Woodrow [SMTP:woodrow_at_nlr.nl]
> Posted At: 09 January 1998 16:02
> Posted To: server
> Conversation: How do I write generic code in triggers?
> Subject: 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