1

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
  • Your code doesn't handle a change of docnumber (not null to not null). – Mat Feb 23 '16 at 12:24
  • @Mat, could you please extend on that a bit? I can't seem to get it. – Mikayil Abdullayev Feb 23 '16 at 13:09
  • update INT_DOC set docnum = 2 where docnum = 1 - your code doesn't do anything in this scenario. – Mat Feb 23 '16 at 13:21
  • what do you mean by sometimes I see a record in INT_DOC table with DOCNUMBER value that does not exist in DOC_IDS table? is that mean the number of rows are different or the values are different? and when you see the different when you do insert ? update? on int_doc. – JSapkota Feb 23 '16 at 13:36
  • @JSapkota, I mean there shouldnət be a record in the INT_DOC table that has a value in its DOCNUMBER column that does not exist in DOC_IDS table. So, if a generated DOCNUMBER value is in INT_DOC.DOCNUMBER table, it must be in the DOC_IDS too – Mikayil Abdullayev Feb 23 '16 at 13:44
1

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.

  • Yeah, I know that in the third update you won't get the DOCNUMBER inserted to DOC_IDS table. But as I've mentioned in my second edit to the question, the third case never happens. That is, DOCNUMBER never gets updated. It's assigned a value inside the BEFORE UPDATE/INSERT trigger and it's never updated anywhere else again. – Mikayil Abdullayev Feb 24 '16 at 7:35
  • @Mikayil Abdullayev, So this mean you should have same number of rows in INT_DOC and DOC_IDS tables but you get the different number of rows right? – JSapkota Feb 24 '16 at 7:45
  • can you add sample row which has inserted into INT_DOC but not in DOC_IDS? – JSapkota Feb 24 '16 at 7:51
  • yes, @JSapkota, that's right. I'll provide the sample you asked for. – Mikayil Abdullayev Feb 24 '16 at 8:20
  • @Mikayil Abdullayev whats about commit statement? How are you making these transaction permanent? – JSapkota Feb 24 '16 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.