Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Passing :NEW and :OLD from triggers to procedures
You can't do it directly, but the following sql*plus script will help you automate the process of putting :new or :old values into a record....
set verify off
set feedback off
set linesize 80
set heading off
prompt declare
prompt &&1._rec &1%rowtype;
prompt begin
select ' &&1._rec."' || column_name || '" := ' ||
':new."' || column_name || '";'
from user_tab_columns
where table_name = upper( '&&1' )
/
SQL> @torowtype emp
declare
emp_rec emp%rowtype
begin
emp_rec."EMPNO" := :new."EMPNO"; emp_rec."ENAME" := :new."ENAME"; emp_rec."JOB" := :new."JOB"; emp_rec."MGR" := :new."MGR"; emp_rec."HIREDATE" := :new."HIREDATE"; emp_rec."SAL" := :new."SAL"; emp_rec."COMM" := :new."COMM"; emp_rec."DEPTNO" := :new."DEPTNO";
then, you can use this to start your trigger with...
On Fri, 14 Feb 1997 08:40:10 -0500, Jim Jenkinson <jjenkins_at_castle.net> wrote:
>Does anyone know if it is possible to pass an entire row from a row
>level trigger to a procedure which accepts input parameters of the same
>%ROWTYPE as the table in question?
>
>For example, on table TEST, I would like to create an after row trigger
>which passes the entire :NEW and :OLD structures to a procedure which
>accepts parameters of TEST%ROWTYPE.
>
>Can this be done? Please post a reply or email privately to
>JJenkins_at_castle.net
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com