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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: