After Update/Insert trigger sometimes fails to do its job

oracletrigger

I've got this trigger:

CREATE OR REPLACE TRIGGER "DOKCENTRA"."INTDOC_AFTER" AFTER
  UPDATE OR INSERT
    ON "DOKCENTRA"."INT_DOC" FOR EACH ROW
BEGIN 
IF UPDATING THEN
 IF (
     :NEW.DOCNUMBER   IS NOT NULL
   AND :OLD.DOCNUMBER IS NULL) THEN
   INSERT
   INTO
     DOC_IDS VALUES
     (
       :NEW.DOCNUMBER
     );
 END IF;
ELSIF INSERTING THEN
  IF (:NEW.DOCNUMBER IS NOT NULL) THEN
    INSERT
    INTO
      DOC_IDS VALUES
      (
        :NEW.DOCNUMBER
      );
  END IF;
END IF;
END;

I think it's obvious what it does but to make it clear let me give a brief explanation. In my INT_DOC table there's DOCNUMBER column. I assign a value to this column in Before Update trigger. And in the above trigger, which of course should fire after the Before Update Trigger, I check if the DOCNUMBER has value (and if it's for the first time) and if it does, I write that value to another table called DOC_IDS. The problem is, the above trigger sometimes, although very rarely, fails to insert the :NEW.DOCNUMBER to the DOC_IDS table. That is, sometimes I see a record in INT_DOC table with DOCNUMBER value that does not exist in DOC_IDS table.The DOC_IDS table has only one column called DOCID and it's part of a UNIQUE KEY constrsraint. Do you see any problem with this trigger?

EDIT:

I think you'll get a better idea if I post the BEFORE TRIGGER too:

create or replace
TRIGGER "DOKCENTRA"."INTDOC_BEFORE" BEFORE
  UPDATE OR
  INSERT ON "DOKCENTRA"."INT_DOC" FOR EACH ROW
 BEGIN
 IF INSERTING THEN
 IF (:NEW.CONFIRMED=1 AND :NEW.DOCNUMBER IS NULL) THEN
    SELECT DOCUMENTS.F_GENERATE_DOCNUMBER(:NEW.REGUSER,2)
    INTO :NEW.DOCNUMBER
    FROM DUAL;
  END IF;
 ELSIF UPDATING THEN
  IF (:NEW.CONFIRMED=1 AND :NEW.DOCNUMBER IS NULL) THEN
    SELECT DOCUMENTS.F_GENERATE_DOCNUMBER(:NEW.EDITUSER,2)
    INTO :NEW.DOCNUMBER
    FROM DUAL;
  END IF;
END IF;
END;

And the only place I set DOCNUMBER is this trigger.

EDIT2:

CREATE TABLE INT_DOC(
   DOCID VARCHAR2(50) NOT NULL,
   RECEIVEDDATE DATE NOT NULL,
   SENDERFULLNAME VARCHAR2(100) NOT NULL,
   SUBJECT VARCHAR2(200) NOT NULL,
   CONFIRMED NUMBER(1,0) NOT NULL DEFAULT 0,
   DOCNUMBER VARCHAR2(10),
  CONSTRAINT INT_DOC_PK PRIMARY KEY 
  (
    DOCID 
  )
  ENABLE 
);
ALTER TABLE INT_DOC
ADD CONSTRAINT INT_DOC_UK1 UNIQUE 
(
  DOCNUMBER 
)
ENABLE;

CREATE TABLE DOC_IDS(
 DOCID VARCHAR2(10)
);
ALTER TABLE DOC_IDS
ADD CONSTRAINT DOC_IDS_UK1 UNIQUE 
(
  DOCID 
)
ENABLE;

INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,CONFIRMED)
VALUES('AFFXRARA1',to_date('01.01.2015','DD.MM.YYYY'),'John Saunders','SomeSubject',1);--CONFIRM set to 1 at insert
INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('AFCCYARA1',to_date('25.10.2015','DD.MM.YYYY'),'Steve Wonder','AnotherSubject');
INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');

UPDATE INT_DOC SET CONFIRMED=1 WHERE DOCID='AFCCYARA1';--CONFIRM set to 1 with update

Here's the sample record in INT_DOC table which has the DOCNUMBER column value that does not exist in the DOC_IDS table:

           DOCID                       RECEIVEDDATE             SENDERFULLNAME                 SUBJECT                      CONFIRMED        DOCNUMBER
---------------------------------- |-----------------------|----------------------|-----------------------------------|------------------|------------
6A5889BB0190D0211A991F47BB19A777        23.02.2016 10:24:17       Rowan Priatski           Amendment to the last decree            1               1602000097

Best Answer

While testing with random data the above trigger worked perfectly for insertion. But as Mat said in the comment, while updating the DOCNUMBER column of INT_DOC the after update trigger failed to update that value in DOC_IDS table. Here is the output of the sample test:

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');   

1 row created.

SQL> select * from doc_ids;

no rows selected

SQL> update int_doc set confirmed=1,docnumber=1 where docid='BRTYRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,confirmed)
VALUES('AFFXRARA1',to_date('12.07.2015','DD.MM.YYYY'),'Jay Hons','YetAnotherSubject',1);

1 row created.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1


SQL> update int_doc set docnumber=2 where docid='AFFXRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1
2

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1
2

SQL> update INT_DOC set docnumber = 3 where docnumber = 1;

1 row updated.

SQL> select docnumber from int_doc;

DOCNUMBER
----------
3
2

SQL> select * from doc_ids;

DOCID
----------
1
2

Here i got the DOCNUMBER 3 in INT_DOC table but not in the DOC_IDS table.