The Oracle database I am using has a stored procedure that I would like to call from within a custom trigger. Would...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.