CODEWITHSUNDEEP
sqloracle-databasetriggerssequence
whatevermike
whatevermike

Reputation: 196

oracle trigger synthetic key

I'm trying to create a synthetic key with a sequence (attribute_1). Im not sure my code is working right as it's the first time i've done anything like this. I'm just looking for general pointers or tips about what im doing.

CREATE TABLE entity
(
attribute_1 INT NOT NULL PRIMARY KEY,
attribute_2 VARCHAR2(5),
attribute_3 NOT NULL VARCHAR2(5)
);

CREATE SEQUENCE attribute_1_seq
START WITH 1
INCREMENT BY 1
NOCACHE;

CREATE TRIGGER attribute_1_trig
BEFORE INSERT ON entity
FOR EACH ROW BEGIN
    SELECT attribute_1_seq.NEXTVAL INTO :new.attribute_1 FROM dual;
    END;
    /

Upvotes: 0

Views: 64

Answers (1)

kevinskio
kevinskio

Reputation: 4551

Your trigger will work but what if you want to supply an ID number? Some apps will insert into a parent and use the id as a foreign key to child tables. In this case you might want to call the sequence directly so you can reuse it.

This trigger will allow you to either insert null as the primary key or supply one

CREATE OR REPLACE TRIGGER entity_Id_TRG BEFORE INSERT OR UPDATE ON entity
FOR EACH ROW
BEGIN
  if inserting and :new.attribute_1 is NULL then
  SELECT attribute_1_SEQ.nextval into :new.attribute_1 FROM DUAL;
  end if;

END;

Upvotes: 1

Related Questions