Re: Creating a trigger that calls a stored procedure

On 22 Dec 2002 19:31:50 -0800, higuey77_at_yahoo.com (Elie) wrote:

>Hi,
>
>Using JAVA (JDBC), I'm trying to create a trigger that calls a stored
>procedure. However there seems to be something wrong with my syntax
>because when I go to the database and check the status of the trigger
>it's invalid and as a result I am unable to call it. The stored
>procedure that the trigger is trying to call is valid and exist in the
>db.
>
>
>Stored Procedure Code (which I believe is correct)is listed below.
>
>String proc= "CREATE OR REPLACE PROCEDURE stored_proc
>(parameter1 IN NUMBER, parameter2 IN NUMBER)
>AS LANGUAGE JAVA NAME 'myClass.method_one(java.lang.Integer,
>java.lang.Double)';";
>
>Trigger Code is listed below
>
>String trig= "CREATE OR REPLACE TRIGGER myTrigg
> AFTER INSERT ON student_billings
> FOR EACH ROW
> BEGIN
> call stored_proc(:new.parameter1, :new.parameter2);
> END;";
>
>Does anyone notice something wrong.
>
>Thanks in Advance

There are several things wrong
- You shouldn't create triggers on the fly from a client program unless you want your DBA stab a knife in your back - If you want to have newlines in the trigger code you simply should use
"create or replace trigger mytrigg"||chr(10)|| "after insert on student_billings "||chr(10)||

etc.
You seem to think you can format a hardcoded literal this way. Well, you can't, not in any procedural programming language

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Dec 23 2002 - 00:49:01 CST