Toad World® Forums

ORA-01407: cannot update ("Database field") to NULL

Hi

Just got this error while I was entering some records. On this database, I've a trigger before insert/update :

BEGIN

IF INSERTING THEN
:new.dat_insert := SYSDATE;
:new.mat_insert := pk_base.f_get_matr;
:new.dat_update := SYSDATE;
:new.mat_update := pk_base.f_get_matr;
:new.dat_valid_from := NVL(:new.dat_valid_from, TRUNC(SYSDATE));
:new.dat_valid_to := NVL(:new.dat_valid_to, TRUNC(SYSDATE) + 30);
ELSIF UPDATING THEN
:new.dat_update := SYSDATE;
:new.mat_update := pk_base.f_get_matr;
END IF;
END;

Dates should be filled in with the trigger, but it doesn't when I click on the commit :

Edit : in the other editor, it works well :

Regards

Martin

Hi Martin,

Could you pls provide your ddl script of table to us?

we didn’t reproduce your issue in our local.

Michael

Hi Michael

My environment :

  • SQL Nav 7.3 beta 32 bit, but it also occurred in former versions like 7.2

  • OS : Win 7 64 bit pro

  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Here the DDL :

– Start of DDL Script for Table PLDA.S_TARIC_CEE_NEW

– Generated 20/05/2016 09:53:39 from PLDA@D_plda.world

CREATE TABLE s_taric_cee_new

(cod_pays VARCHAR2(2 BYTE) NOT NULL,

cod_taric VARCHAR2(10 BYTE) NOT NULL,

dat_valid_from DATE NOT NULL,

dat_valid_to DATE,

dat_insert DATE,

dat_update DATE,

mat_insert NUMBER(5,0),

mat_update NUMBER(5,0),

remarque VARCHAR2(100 BYTE))

SEGMENT CREATION IMMEDIATE

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE plda

STORAGE (

INITIAL 1024000

NEXT 1024000

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

NOLOGGING

/

– Constraints for S_TARIC_CEE_NEW

ALTER TABLE s_taric_cee_new

ADD CONSTRAINT pk_s_taric_cee_new PRIMARY KEY (cod_pays, cod_taric)

USING INDEX

PCTFREE 5

INITRANS 2

MAXTRANS 255

TABLESPACE indx_plda

STORAGE (

INITIAL 1024000

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

/

– Triggers for S_TARIC_CEE_NEW

CREATE OR REPLACE TRIGGER s_taric_cee_new_b_ins_upd

BEFORE

INSERT OR UPDATE

ON s_taric_cee_new

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

vn_count PLS_INTEGER;

BEGIN

IF INSERTING THEN

:new.dat_insert := SYSDATE;

:new.mat_insert := pk_base.f_get_matr;

:new.dat_update := SYSDATE;

:new.mat_update := pk_base.f_get_matr;

:new.dat_valid_from := NVL(:new.dat_valid_from, TRUNC(SYSDATE));

:new.dat_valid_to := NVL(:new.dat_valid_to, TRUNC(SYSDATE) + 30);

ELSIF UPDATING THEN

:new.dat_update := SYSDATE;

:new.mat_update := pk_base.f_get_matr;

END IF;

SELECT COUNT(*)

INTO vn_count

FROM s_taric_cee

WHERE cod_pays = :new.cod_pays AND cod_taric = :new.cod_taric;

IF vn_count = 1 THEN

vn_count := 1 / 0; – Code taric existe déjà dans s_taric_cee !

END IF;

END;

/

– Comments for S_TARIC_CEE_NEW

COMMENT ON TABLE s_taric_cee_new IS ‘Codes tarics qui ont été ajoutés au courant du mois et ne sont donc pas encore présents dans s_taric_cee… et s_taric_meas. La table sert aux tests dynamiques afin de ne pas exécuter du codes pour ces nouveaux tarics non documentés’

/

COMMENT ON COLUMN s_taric_cee_new.cod_pays IS ‘Code Pays’

/

COMMENT ON COLUMN s_taric_cee_new.cod_taric IS ‘Code taric’

/

COMMENT ON COLUMN s_taric_cee_new.dat_insert IS ‘Date insertion record’

/

COMMENT ON COLUMN s_taric_cee_new.dat_update IS ‘Date modification record’

/

COMMENT ON COLUMN s_taric_cee_new.dat_valid_from IS ‘Code valide a partir de …’

/

COMMENT ON COLUMN s_taric_cee_new.dat_valid_to IS ‘Code valide jusqu’‘au …’

/

COMMENT ON COLUMN s_taric_cee_new.mat_insert IS ‘Matricule insertion record’

/

COMMENT ON COLUMN s_taric_cee_new.mat_update IS ‘Matricule modification record’

/

COMMENT ON COLUMN s_taric_cee_new.remarque IS ‘Remarque éventuelle’

/

– End of DDL Script for Table PLDA.S_TARIC_CEE_NEW

Regards

Martin

Hi Martin,

we sill looking into this, because we work fine when use your script in our local.

at last, you can have a try to reset your user profile.

  1. close SQL Navigator

  2. rename registry key “HKEY_CURRENT_USER\Software\Dell\SQL Navigator 7.3.0 beta” to " SQL Navigator 7.3.0 beta bak"

  3. rename folder “%appdata%\Dell\SQL Navigator 7.3.0 beta” to “SQL Navigator 7.3.0bak”

  4. now start up SQL Navigator again

  5. Please make sure “Cancel” is selected when SQL Navigator prompts the import profile settings dialog

Starting with fresh settings helps to avoid errors from old settings. If this doesn’t stop the error, please send us support bundle files. (michael.luo@quest.com)

Meanwhile, we will keep eyes on this.

Thanks,

Michael

Hi Michael

Already done for this problem www.toadworld.com/.../27010

Problems are resolved after reset, but after a short while they come back.

The scrolling bug, I’m the only one to have, but the sort order, we all have sometimes.

I can’t reproduce it on demand on a new session, but when the session “gets wrong”, I can reproduce it all the time. It’s related somewhere to the session.

I’ve send the files to your email adres.

Regards

Martin

Hi Martin,

we have get your support bundle files by your email.

we still looking into your issue, it seem that some random clue cause。

Regards,

Michael