By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,706 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,706 IT Pros & Developers. It's quick & easy.

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

P: 10
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
Share this Question
Share on Google+
3 Replies


10K+
P: 13,264
How are you executing the insert statements? Are they inside a loop?
Jul 22 '08 #2

amitpatel66
Expert 100+
P: 2,367
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

100+
P: 153
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.