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; /
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2003