471,311 Members | 1,772 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,311 software developers and data experts.

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

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.atas,:new.bawah,:new. dmtr,:new.pjng,:new.awal,:new.akhir,:new.kap,:new. isi,:new.ksb,:new.ksr,:new.cad,:new.repsb,:new.wsu cc,:new.tgl_init,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
Jul 22 '08 #1
3 1462
r035198x
13,262 8TB
How are you executing the insert statements? Are they inside a loop?
Jul 22 '08 #2
amitpatel66
2,367 Expert 2GB
In order to make use of a sequence, you will need to do something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Create or replace trigger bef_ins_primer
  3. before insert on dpsj_primer
  4. for each row
  5. begin
  6. SELECT prim.nextval INTO :new.column_name FROM DUAL;
  7. end;
  8.  
  9.  
Jul 22 '08 #3
Dave44
153 100+
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:
Expand|Select|Wrap|Line Numbers
  1. [138]dave@> drop table t;
  2.  
  3. Table dropped.
  4.  
  5. Elapsed: 00:00:00.84
  6. [138]dave@> create table t (
  7.   2  my_id  number primary key,
  8.   3  var    varchar2(4000)
  9.   4  );
  10.  
  11. Table created.
  12.  
  13. Elapsed: 00:00:00.28
  14. [138]dave@> create sequence t_sn;
  15.  
  16. Sequence created.
  17.  
  18. Elapsed: 00:00:00.01
  19. [138]dave@> create or replace trigger t_trg
  20.   2  before insert on t for each row
  21.   3  begin
  22.   4    :new.my_id := t_sn.nextval;
  23.   5  end;
  24.   6  /
  25.  
  26. Trigger created.
  27.  
  28. Elapsed: 00:00:00.04
  29. [138]dave@> insert into t (var) select level from dual connect by level <= 10000;
  30.  
  31. 10000 rows created.
  32.  
  33. Elapsed: 00:00:01.14
  34. [138]dave@> drop trigger t_trg;
  35.  
  36. Trigger dropped.
  37.  
  38. Elapsed: 00:00:00.04
  39. [138]dave@> insert into t select t_sn.nextval, level from dual connect by level <= 10000;
  40.  
  41. 10000 rows created.
  42.  
  43. Elapsed: 00:00:00.37
  44.  
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.
Jul 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by bolidev | last post: by
2 posts views Thread by Nagib Abi Fadel | last post: by
reply views Thread by lkrubner | last post: by
13 posts views Thread by Jacek Dziedzic | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.