Oracle SQL - Trigger inserting into table passing null value from sequence

Question

I have two table, TABLE_A and TABLE_B. When something gets inserted into TABLE_A there is a trigger that also inserts the data into TABLE_B. TABLE_A has an ID column which is populated using a sequence. This id then is also inserted into TABLE_B. This is the whole DDL for this:

CREATE TABLE  "TABLE_A" 
   (    "ID" NUMBER(8,0) NOT NULL ENABLE, 
    "COLUMN1" NUMBER(8,0) NOT NULL ENABLE, 
    "COLUMN2" NUMBER(4,0) NOT NULL ENABLE
   )
/

CREATE TABLE  "TABLE_B" 
   (    "ID" NUMBER(8,0) NOT NULL ENABLE, 
    "COLUMN1" NUMBER(8,0) NOT NULL ENABLE, 
    "COLUMN2" NUMBER(4,0) NOT NULL ENABLE
   )

/   

  CREATE UNIQUE INDEX  "AID_PK" ON  "TABLE_A" ("ID")
/

ALTER TABLE  "TABLE_A" ADD CONSTRAINT "AID_PK" PRIMARY KEY ("ID")
  USING INDEX  "AID_PK"  ENABLE
/


create or replace TRIGGER my_trigger
BEFORE INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TABLE_B(
ID,
COLUMN1,
COLUMN2)
VALUES(
:new.ID,
:new.COLUMN1,
:new.COLUMN2);
END IF;
END;

/
ALTER TRIGGER  "my_trigger" ENABLE
/

 CREATE SEQUENCE   "MY_SEQ"  MINVALUE 1 MAXVALUE 999999999999999 INCREMENT BY 1 START WITH 5002 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL;


/

CREATE OR REPLACE EDITIONABLE TRIGGER  "MYSEQ_SEQ_IOT" 
before insert on table_a
for each row
begin
select MY_SEQ.nextval into :new.id from dual;
end;

/
ALTER TRIGGER  "MYSEQ_SEQ_IOT" ENABLE
/

Now when I run this statement:

INSERT INTO PER_ART(
    COLUMN1,
    COLUMN2
    )
VALUES(
    1111111,
    2222222); 

I get this error:

ORA-01400: cannot insert NULL into ("TABLE_B"."ID")
ORA-06512: at "my_trigger", line 7
ORA-04088: error during execution of trigger 'my_trigger'

Why is the ID null when the sequence should be populating it?

Answer 1

The order in which your two triggers fire is indeterminate. In my opinion, the best solution would be to just use one trigger:

create or replace TRIGGER my_trigger
  BEFORE INSERT ON TABLE_A
  FOR EACH ROW
BEGIN
  select MY_SEQ.nextval
    into :new.id 
    from dual;

  INSERT INTO TABLE_B(
    ID,
    COLUMN1,
    COLUMN2)
  VALUES(
    :new.ID,    -- Or use MY_SEQ.curreval
    :new.COLUMN1,
    :new.COLUMN2);
END;

If you must have two triggers for some reason, then you can control their firing order using the FOLLOWS and PRECEDES clauses of the CREATE TRIGGER statement. Refer to the documentation for details on controlling trigger order.