473,226 Members | 1,411 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 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 1607
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

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

Similar topics

14
by: bolidev | last post by:
I'm new to SQL and can't figure out how to update my table (StoreItemStatus) that contains the current status for items in each store (STORE_KEY, ITEM_KEY, STATUS,...). I get updated status info...
2
by: Nagib Abi Fadel | last post by:
Hi , i'm doing the planification of the courses in a university. I have definned the following tables: 1- Table DAY representing a day (day_id,year,month ...) 2- Table PERIODE representing an...
0
by: lkrubner | last post by:
The idea I'm trying to get at is that I want the tag info for the tag "photography", and I want the date, and I want a count of any comments a tag may have. This following query gets back all the ...
13
by: Jacek Dziedzic | last post by:
Hi! <OT, background> I am in a situation where I use two compilers from different vendors to compile my program. It seems that recently, due to a misconfiguration, library conflict or my...
9
by: Pyenos | last post by:
Approach 1: class Class1: class Class2: def __init__(self):self.variable="variable" class Class3: def method():print Class1().Class2().variable #problem Approach 1.1:
9
by: cdriccio | last post by:
Hello, I'm trying to make a gif image out of a total of 5 (currently) images. One background image and 4 small icons. This image also includes text of a specific font. Here are my issues. ...
3
by: Shestine | last post by:
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is...
8
by: rbukkara | last post by:
Hi guys, I have some trouble with the following query. Please look into this and lemme know the solution ASAP. It certainly involves aggregations and the 'having clause' BROKER( ID integer...
1
by: aviansh | last post by:
HI Experts, I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare Tables in both tha...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.