I have pre and post update triggers firing on a database table to add sysdate and user to audit fields. The triggers fire perfectly when updating via SQLDeveloper,SQLPlus etc. but fail when attempting updates from my APEX app.
triggers are of the following format:
Preupdate Trigger Format is as follows:
create or replace trigger <preupdate_mytable>
before update
on <mytable>
for each row
begin
select sysdate, user
into :new.lastupdate, :new.updatedby
from dual;
end;
/
Post_update trigger writing to an audit table is of the following format:
create or replace TRIGGER postupdate_mytable
after update or delete on mytable
referencing
new as new
old as old
for each row
begin
insert into <AUDIT_TABLE>
( column1, column2, column3)
values
(:old.column1, :old.column2, :old.column3);
end;
I get the following error message when attempting to apply changes via the apex form:
ORA-20505: Error in DML: p_rowid=1, p_alt_rowid=ID, p_rowid2=, p_alt_rowid2=. ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at <preupdate_mytable>, line 2 ORA-04088: error during execution of trigger '06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at <preupdate_mytable>'
If the database triggers are disabled the update works normally.
I thought the problem may be with date formats and removed the date components of the update trigger Still got errors