FOR EACH ROW trigger problem
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?