Oracle PL / SQL: Forwarding an entire row to a procedure using a trigger

Question

We have a Row-Level trigger from PL / SQL Oracle (10i) that is responsible for three independent tasks. Since the trigger is relatively cluttered in this way, I want to export these three tasks into three stored procedures. I was thinking about using the my_table%ROWTYPE or perhaps a collection type for procedures, but my main problem is how to populate these parameters.

Is it possible to easily add a whole line :NEW trigger to a single variable? Until now, the only way I could figure out was to assign each field separately to a variable, which is not entirely satisfactory when looking at code maintenance, etc.

Something like

 SELECT :NEW.* INTO <variable> FROM dual; 

would be preferable. (I have not tried this in fact, but I believe that it did not work)

Answer 1

It's impossible.

Maybe my answer to another question may help.

Answer 2

In the vast majority of cases, the only way to assign new values ​​in a row to the variable% ROWTYPE is to explicitly assign each column. Something like

 CREATE OR REPLACE TRIGGER some_trigger_name BEFORE INSERT OR UPDATE ON some_table FOR EACH ROW DECLARE l_row some_table%rowtype; BEGIN l_row.column1 := :NEW.column1; l_row.column2 := :NEW.column2; ... l_row.columnN := :NEW.columnN; procedure1( l_row ); procedure2( l_row ); procedure3( l_row ); END; 

If your table is declared based on an object: NEW will be an object of this type. Therefore, if you have a table like

 CREATE OR REPLACE TYPE obj_foo AS OBJECT ( column1 NUMBER, column2 NUMBER, ... columnN NUMBER ); CREATE TABLE foo OF obj_foo; 

then you can declare procedures that take input parameters of type OBJ_FOO and call them directly from your trigger.

The suggestion in another thread about selecting a row from a table in the AFTER INSERT / UPDATE stream, unfortunately, usually does not work. This will usually result in the exclusion of the mutating table.

  1 create table foo ( 2 col1 number, 3 col2 number 4* ) SQL> / Table created. SQL> create procedure foo_proc( p_foo in foo%rowtype ) 2 as 3 begin 4 dbms_output.put_line( 'In foo_proc' ); 5 end; 6 / Procedure created. SQL> create or replace trigger trg_foo 2 after insert or update on foo 3 for each row 4 declare 5 l_row foo%rowtype; 6 begin 7 select * 8 into l_row 9 from foo 10 where col1 = :new.col1; 11 foo_proc( l_row ); 12 end; 13 / Trigger created. SQL> insert into foo values( 1, 2 ); insert into foo values( 1, 2 ) * ERROR at line 1: ORA-04091: table SCOTT.FOO is mutating, trigger/function may not see it ORA-06512: at "SCOTT.TRG_FOO", line 4 ORA-04088: error during execution of trigger 'SCOTT.TRG_FOO' 

Answer 3

Use SQL to generate SQL;

 select ' row_field.'||COLUMN_NAME||' := :new.'||COLUMN_NAME||';' from ALL_TAB_COLUMNS cols where cols.TABLE_NAME = 'yourTableName' order by cols.column_name 

Then copy and paste the output.

Answer 4

This is similar to Justins solution, but a little shorter (without entering the left side of each job):

 -- use instead of the assignments in Justins example: select :new.column1, :new.column2, ... :new.columnN, into l_row from dual;