Re: before/after image assignment in triggers
Date: 1996/01/27
Message-ID: <4ebnvb$2ua_at_inet-nntp-gw-1.us.oracle.com>#1/1
mark rostron <markro2_at_atlantis.rosemount.com> wrote:
>Hi,
>We are doing a lot of parallel development to table behaviour with different
>programming groups needing to set values in a table row prior to insertion/update.
>In order to avoid treading on each others' toes, we have decided to minimise
>specific coding in the trigger and do it all in separate procedures, and merge them
>in the trigger, somehow.
>I also want to avoid creating procedures with long parameter lists on account of
>they become a maintainer's nightmare.
>Now, I know the following syntax is valid:
>--
>declare
> row table%rowtype;
>begin
> procedure_call(row);
>end;
>--
>where the procedure_call definition is:
> procedure_call(p_row inout table%rowtype)
>Being able to pass an image of the table row will solve a maintenance problem, BUT:
> row := :new;
>or whatever it's correct definition is. Can anyone please tell me a way?
>ps: I have tried the following alternatives:
>- passing the id of the row; doesn't work till the row exists
>- post-processing instead of pre-processing: has some drawbacks
>If all else fails, we'll revert to using specialist parameter calls.
>thanx,
>mr
I would use SQL to write SQL to automate the trigger definition. The following example shows how to do it for the EMP table for example:
REM Create a series of procedures that work on RECORDS...
create or replace procedure do_something( x in out emp%rowtype )
as
begin
x.ename := upper(x.ename);
end do_something;
/
REM Now we will use the data dictionary to write our trigger to a tmp file
set heading off
set feedback off
spool tmp.sql
prompt create or replace trigger emp_bifer
prompt before insert on emp
prompt for each row
prompt declare
prompt x emp%rowtype;;
prompt begin
prompt select
select decode( column_id, 1, '', ',' ), ':new.'||column_name
from user_tab_columns where table_name = 'EMP'
order by column_id
/
prompt into x from dual;;
REM put your calls here:
prompt do_something( x );;
prompt select
select decode( column_id, 1, '', ',' ), 'x.'||column_name
from user_tab_columns where table_name = 'EMP'
order by column_id
/
prompt into
select decode( column_id, 1, '', ',' ), ':new.'||column_name
from user_tab_columns where table_name = 'EMP'
order by column_id
/
prompt from dual;;
prompt end;;
prompt /
spool off
set heading on
set feedback on
_at_tmp.sql
So the above generated a trigger that looks like:
create or replace trigger emp_bifer
before insert on emp
for each row
declare
x emp%rowtype;
begin
select :new.EMPNO, :new.ENAME, :new.JOB
, :new.MGR, :new.HIREDATE, :new.SAL, :new.COMM
, :new.DEPTNO
into x from dual;
do_something( x );
select x.EMPNO, x.ENAME, x.JOB, x.MGR
, x.HIREDATE, x.SAL, x.COMM, x.DEPTNO
into :new.EMPNO, :new.ENAME, :new.JOB
, :new.MGR, :new.HIREDATE, :new.SAL, :new.COMM
, :new.DEPTNO
from dual;
end;
/
Hope this helps...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Received on Sat Jan 27 1996 - 00:00:00 CET