Skip to Main Content

trigger for creating new id only if not entered

902667Feb 3 2012 — edited Feb 6 2012
Hi,

I have table:
CREATE TABLE TGAP_EDGE
( "EDGE_ID" NUMBER(6,0) NOT NULL ENABLE,
"IMP_LOW" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
"IMP_HIGH" NUMBER(6,0) DEFAULT NULL)

CONSTRAINT "TGAP_EDGE_PK" PRIMARY KEY ("EDGE_ID", "IMP_LOW")

And primary key is composed from edge_id and imp_low.

I am trying create trigger witch generated new id only if I don’t inserted it.

For example:
A) Insert into tgap_edge ( imp_low, imp_high) values ( 2, 10);
-> New record would be created with new id.

B) Insert into tgap_edge (edge_id, imp_low, imp_high) values (10, 4, 20);
-> I am entering id. New id would not be created.

I try create trigger, but doesn’t work.
create or replace
TRIGGER TBI_TGAP_EDGE
BEFORE INSERT ON TGAP_EDGE
FOR EACH ROW
declare
count_exist number := 0;
begin
select count(:old.edge_id) into count_exist from dual;

if count_exist = 0 then
select tgap_edge_id_seq.nextval into :new.edge_id from dual;
else
insert into tgap_edge (edge_id, imp_low, imp_high)
values (:old.edge_id, :old.imp_low, :old.imp_high);
end if;
END;

What is the simplest solution?
Thank you
Comments
Post Details
Added on Feb 3 2012
12 comments
352 views