Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FOR EACH ROW trigger problem

250811Apr 26 2002
I have been trying to create an after update trigger. I want to check whether either of 2 columns have changed, and if they have then run a procedure

This is the trigger
CREATE OR REPLACE TRIGGER "SCHEMA_OWNER".after_update_trg
AFTER UPDATE of column1, column2 ON mytable
FOR EACH ROW
DECLARE
v_return_val varchar2(2000);
err_return_val varchar2(200);

BEGIN

if :old.column1 != :new.column1 then

this_schema.procedure1(v_return_val,'mytable','column1'); #it errors here

if (v_return_val is not null) then
schema_error.insert_log(err_return_val, v_return_val, v_return_val, 'n/a', 'n/a');
end if;
end if;
END;

But when I change a column I get this error

The following error has occurred:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "TRB_OWNER.MIM_TT_AU_TRG", line 12
ORA-04088: error during execution of trigger 'TRB_OWNER.MIM_TT_AU_TRG'

Details:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "TRB_OWNER.MIM_TT_AU_TRG", line 12
ORA-04088: error during execution of trigger 'TRB_OWNER.MIM_TT_AU_TRG'


The frustrating thing is that I have after insert triggers that run the same procedure that work fine, so the problem isn't the procedure.

If you take out FOR EACH ROW it runs the procedure fine, but I can't use the :use or :new without it.

Any ideas?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 27 2002
Added on Apr 26 2002
11 comments
504 views