Oracle trigger which doesn't work

Question

Hi all,

I've create a trigger which work on 2 tables like this: - when insert a new row on table A (GEDDFILES) then a number field on table B(GEDDIRS) will be increased.

Code is getting compiled without errors but it doesn't work. the code is bellow:

CREATE OR REPLACE TRIGGER "A"."TRGMAJNRFIC" AFTER
INSERT ON "A"."GEDFILES" FOR EACH ROW DECLARE 
 wtmpnrfic number;

BEGIN 
 SELECT MAX(OBJNRFICHIERS) INTO wtmpnrfic from GEDDirs WHERE ID= :new.ID;
 UPDATE GEDDirs SET OBJNRFICHIERS = wtmpnrfic+1 WHERE ID=:new.id;
END;

Tables are normalized and PK from GEDDIRS(field ID) is FK in table GEDFILES (field ID)

I can't figure out where is the problem, even is a little one.

The field OBJNRFICHIERS from table GEDFILES is null. Can be this a problem when i'm trying to increase it's value?

Thanks in advance!

Best regards,

Answer 1

Simple question: Have you enabled your trigger?

http://www.techonthenet.com/oracle/triggers/enable.php

Edit: Now I saw that you described that OBJNRFICHIERS from table GEDFILES is null. Put 0 (zero) on that column and run a test... Probably this is the problem! :)

Answer 2

i've resolved it. the problem was that the field OBJNRFICHIERS was null. the trigger code is bellow

CREATE OR REPLACE TRIGGER "A"."TRGMAJNRFIC" AFTER
INSERT ON "A"."GEDFILES" FOR EACH ROW DECLARE 
 wtmpnrfic number;

BEGIN 
 SELECT MAX(OBJNRFICHIERS) INTO wtmpnrfic from GEDDirs WHERE ID= :new.ID;
 IF wtmpnrfic IS NULL then wtmpnrfic :=0;
 ELSE wtmpnrfic := wtmpnrfic+1;
 UPDATE GEDDirs SET OBJNRFICHIERS = wtmpnrfic+1 WHERE ID=:new.id;
 END IF;
END;

thank you all for the suggestions, and also other suggestions are welcomed!

best regards,

Answer 3

This is a perfect place to use NVL2 to make the code a bit cleaner:

CREATE OR REPLACE TRIGGER "A"."TRGMAJNRFIC"
  AFTER INSERT ON "A"."GEDFILES"
  FOR EACH ROW
DECLARE  
  wtmpnrfic number;  
BEGIN  
  SELECT MAX(OBJNRFICHIERS)
    INTO wtmpnrfic
    from GEDDirs
    WHERE ID = :new.ID; 

  UPDATE GEDDirs
    SET OBJNRFICHIERS = NVL2(wtmpnrfic, 0, wtmpnrfic+1);
    WHERE ID = :new.ID; 
  END IF; 
END;

Or, if you like, you could use COALESCE to replace the NVL2 line above:

    SET OBJNRFICHIERS = COALESCE(wtmpnrfic+1, 0);

Or, I suppose, NVL would work just as well:

    SET OBJNRFICHIERS = NVL(wtmpnrfic+1, 0);

Share and enjoy.