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?

If the trigger is called my_trigger then ALTER TRIGGER "my_trigger" ENABLE won't work, because "my_trigger" is not the same as my_trigger. — William Robertson
– William Robertson, Commented Dec 7, 2019 at 0:03

AndyDan · Accepted Answer · 2019-12-06 19:10:13Z

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.

answered Dec 6, 2019 at 19:10

AndyDan

7592 gold badges14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2924127 Over a year ago

Thanks for the reply. I didn't know this. To confirm then it would be FOR EACH ROW FOLLOWS MYSEQ_SEQ_IOT? I have tried it and it seems to work, but I just want to confirm this is not a coincidence and the right approach!

AndyDan Over a year ago

If it works, then it's probably correct. I have not actually used the FOLLOWS/PRECEDES clauses myself.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related