Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: calling a procedure from a trigger in oracle 7.3

Re: calling a procedure from a trigger in oracle 7.3

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Apr 1998 12:49:11 GMT
Message-ID: <353b43cf.1737458@192.86.155.100>


A copy of this was sent to "M.BALAKRISHNAN" <m_balakrishnan_at_hotmail.com> (if that email address didn't require changing) On Mon, 20 Apr 1998 08:54:47 -0700, you wrote:

>Jonathan Gennick wrote:
>>
>> On Thu, 16 Apr 1998 14:40:10 -0500, John Franklyn
>> <john.franklyn_at_workplacesystems.com> wrote:
>>
>> >Does anyone have an example of how to call a procedure from within a
>> >trigger in Oracle 7.3? I have a trigger the calculates area totals. I
>> >would like to pass the area total to a procedure, for update of another
>> >table.
>
>I rememeber reading somewhere in the amazing number of ORACLE books that
>are available that 'Stored procedures cannot be invoked from a DB
>trigger...'.. Perhaps it would be better to check with ORACLE itself!
>
>M. Balakrishnan

Triggers are written in pl/sql.
stored procedures are written in pl/sql.

It is natural for a trigger to call a procedure. Its as simple as:

create procedure foo( x in number )
as
begin

   ....;
end;
/

create trigger my_trigger
on T
after insert or update or delete
for each row
begin

   foo( :new.data );
end;
/

for example. The called procedure is not allowed to commit or perform other transactional things (like DDL for example) since we are in the middle of processing a DML statement. Other then then, calling procedures from triggers is as easy as just doing it.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Apr 20 1998 - 07:49:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US