Home » SQL & PL/SQL » SQL & PL/SQL » Calling procedure from trigger (Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Windows 7)
Calling procedure from trigger [message #657633] Wed, 16 November 2016 09:41 Go to next message
SW26477
Messages: 9
Registered: November 2014
Location: Location
Junior Member
Hi,

Can anyone give me an example where we can call pkg.proc(pass bunch of data) from before update trigger.

I am creating a new before update trigger, where I am going to select some data from different table and pass this data to new pkg.proc().

I want something like this:
Create or replace trigger BUR
Before UPDATE
on my_table
REFERENCING NEW AS NEW OLD AS OLD
for each row
DECLARE

  cursor c_deatils
      is
	  select tab1.col1, tab1.col2, tab2.col3, tab2.col4, tab2.col5, tab2.col6
	    from tab1 a, tab2 b
		where a.id = b.id;
		
begin

  open c_deatils;
  fetch c_deatils into l_table;
  close c_deatils;
  
for i in l_table.first..l_lat_table.l_table loop
  pkg.proc(l_table(i));
end loop;

end;  

Also please give an example how pkg.proc structure will look like.

Thanks in Advance.

[Updated on: Wed, 16 November 2016 09:42]

Report message to a moderator

Re: Calling procedure from trigger [message #657634 is a reply to message #657633] Wed, 16 November 2016 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 26203
Registered: January 2009
Location: SoCal
Senior Member
Do not do in PL/SQL that which can be done in plain SQL.
L_TABLE must be declared before it can be (ab)used.
Re: Calling procedure from trigger [message #657635 is a reply to message #657634] Wed, 16 November 2016 10:32 Go to previous message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said in his previous topic:

Michel Cadot wrote on Thu, 13 November 2014 07:51

But is it smart to try to workaround good code to make it work in 8i and deliver bad code?

So I think, he now does not care to continue to deliver bad code. Very Happy

Previous Topic: merge statement problem
Next Topic: how to close open cursor when exception
Goto Forum:
  


Current Time: Sat Oct 20 19:31:44 CDT 2018