While editing record, if a mandatory column is left null to be set by trigger an error is raised saying that 'Field ID must have a value' (where ID is an example of column name).
Problem is unsolved since 10.0. Previous versions (9.7) didn't have this issue.
This works when it's done from schema browser data grid but it doesn't when it's done from sql editor.
To reproduce the problem please follow the steps below:
1) Run the script below:
CREATE TABLE TESTCASETABLE
(
ID NUMBER NOT NULL primary key,
NAME VARCHAR2(50 BYTE) NOT NULL,
CREATED DATE NOT NULL
)
/
CREATE OR REPLACE TRIGGER test
BEFORE INSERT OR UPDATE
ON TESTCASETABLE REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
if :new.id is null then
:new.id := 1;
end if;
if :new.created is null then
:new.created := sysdate;
end if;
END;
/
2) go to the editor and run
select rowid, tct.*
from testcasetable tct
3) enter a new row but leave the id null (the trigger should fill this field BEFORE INSERT)
You get the error 'Field ID must have a value'
WORKAROUND
Use Schema Browser
STATUS
Waiting for fix in a future release of Toad for Oracle
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy