Q
Problem solve Get help with specific problems with your technologies, process and projects.

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


Dig Deeper on Oracle stored procedures

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close