i'm having trouble in defining primary key... HELP ME guySSSS

[faathir88]

i'd like to insert lots of data n its hard to determine which field would be the primary key, coz all of them almost similar.

So, i decided to use sequence for its PK by using trigger
here's the code :

Create or replace trigger bef_ins_primer
before insert on dpsj_primer
for each row
begin
insert into dpsj_primer values(:new.sto ,:new.rk,:new.a tas,:new.bawah, :new.dmtr,:new. pjng,:new.awal, :new.akhir,:new .kap,:new.isi,: new.ksb,:new.ks r,:new.cad,:new .repsb,:new.wsu cc,:new.tgl_ini t,prim.nextval) ;
end;

Prim is the sequence's name

when i try 2 insert them, here are the error msg

ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.BEF_INS _PRIMER'
ORA-06512: at "SYSTEM.BEF_INS _PRIMER",


Help me plizzz... thx before

[r035198x]

How are you executing the insert statements? Are they inside a loop?

[amitpatel66]

In order to make use of a sequence, you will need to do something like this:

[code=oracle]

Create or replace trigger bef_ins_primer
before insert on dpsj_primer
for each row
begin
SELECT prim.nextval INTO :new.column_nam e FROM DUAL;
end;

[/code]

[Dave44]

what is the nature of the inserts to the table? Is it 1 row at a time or batches of thousands? reason i ask is that there can be a notable time difference when using a trigger vs just inserting the sequence as part of the insert statement.

observe:

[138]dave@> drop table t;

Table dropped.

Elapsed: 00:00:00.84
[138]dave@> create table t (
  2  my_id  number primary key,
  3  var    varchar2(4000)
  4  );

Table created.

Elapsed: 00:00:00.28
[138]dave@> create sequence t_sn;

Sequence created.

Elapsed: 00:00:00.01
[138]dave@> create or replace trigger t_trg
  2  before insert on t for each row
  3  begin
  4    :new.my_id := t_sn.nextval;
  5  end;
  6  /

Trigger created.

Elapsed: 00:00:00.04
[138]dave@> insert into t (var) select level from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:01.14
[138]dave@> drop trigger t_trg;

Trigger dropped.

Elapsed: 00:00:00.04
[138]dave@> insert into t select t_sn.nextval, level from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.37

It took nearly 3 times longer to use the trigger vs just doing it in the insert. triggers add overhead, it's another call for each row and it takes time. like i said, if you're inserting 1 row at a time, it probably doesnt matter, if you inserting large numbers at a time, it can add up.