Q

Calling a stored procedure from a trigger

The Oracle database I am using has a stored procedure that I would like to call from within a custom trigger. Would you provide an example that demonstrates how to set this up? How do I call a stored procedure from within a trigger and pass parameter between the two?

You call procedures from trigger code just like you call it from anywhere else:

 CREATE OR REPLACE my_procedure (p_variable1 IN my_table.column1%type, p_variable2 IN my_table.column2%type, p_out OUT number) AS BEGIN p_out := p_variable1 + p_variable2 ; END ; / CREATE OR REPLACE TRIGGER my_test_trigger BEFORE INSERT ON my_table DECLARE v_out number ; BEGIN my_procedure(:new.column1, :new.column2, v_out) ; :new.column3 := v_out ; END; /

To test:

 INSERT INTO my_table VALUES (5, 20) ;

The insert will cause the trigger to fire returning the value of 25 (5 + 20) to the v_out variable. I then use this value to populate a third column in the table before doing the insert.

After the insert occurs and you select from the table you get:

 COLUMN1 COLUMN2 COLUMN3 5 20 25

Yes, this is a crude example, but I hope it shows you what you need!

 

For More Information


This was first published in April 2003

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close