Re: Calling Procedures Within Triggers

From: Stefan Grundmann <s.grundmann_at_t-online.de>
Date: Wed, 30 Aug 2000 19:48:32 +0200
Message-ID: <8ojhts$avr$13$1@news.t-online.com>

Hallo Steve!

I prefer the trigger that runs a procedure.

Example: Table T_Example with Trigger TR_example with called procedure proc_example (see below).

create Table T_Example
(

 col_num number(10),
 col_cha  char(2),
 col_var  varchar2(40),
 col_dat  date

);

create or replace
procedure proc_example
(

 v_param1 varchar2,
 d_param2 date,
 n_param3 number

)
begin

   <do_something>;
end;

create or replace
trigger tr_example
before insert or ...
for each row
begin
  proc_example ( v_param1 => :NEW.col_var,

                             d_param2 => :NEW.col_dat,
                             n_param3 => :NEW.col_num
                         );

   ...
end;

For create triggers and the naming of columns (:NEW.col_name / :OLD.col_name) see the Oracle-docu.

Hope this helps,
Stefan.

stevetorch_at_my-deja.com> schrieb in im Newsbeitrag: 8ohh6j$ic3$1_at_nnrp1.deja.com...
> Hi

>

> I am trying to come up with a way to insert new records into a table
> using a trigger. The problem is data that I need comes from more than
> one table. Is there a way to join tables in a trigger or should I use
> a trigger that runs a procedure?

>

> if procedure is the way to go then below is the code that i have but i
> just can't make it work. when it says parameters what Parameters is it
> talking about?

>

> any info is very appreciated
> thank you very much
> Steve

>

> Create or replace trigger theTrigger
> before [or after] .....on tableName
> declare
> begin
> procedure_name(parameters);
> end;
> /

>

> create or replace procedure procedure_name
> is
> cursor my_cursor is select some_column, another_column, more_columns
> from table1, table2
> where table1.value = table2.value order by optional_sort_column;

>

> begin

>

> for rec1 in my_cursor Loop
> insert into tablez (col1, col2, col3) values(rec1.some_column,
> rec1.another_column, rec1.more_columns);
> end loop;
> end;
> /

>
>
>

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Aug 30 2000 - 12:48:32 CDT