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 last published in April 2003

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close