Ask the Expert

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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top