Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 15th, 2004, 10:13 PM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default auto increment primary key-compile error

I have table BILL and ITEM

CREATE TABLE bill (
in_inv NUMBER PRIMARY KEY,
bill VARCHAR2 (512),
ship VARCHAR2 (512),
invdate DATE,
invno VARCHAR2 (12),
po VARCHAR2 (12),
dn VARCHAR2 (15),
cur VARCHAR2 (15),
pri VARCHAR2 (35),
term VARCHAR2 (30) );

CREATE TABLE item (
in_line NUMBER PRIMARY KEY,
line NUMBER,
invno VARCHAR2 (12),
po1 VARCHAR2 (10),
stock1 VARCHAR2 (2000),
qty1 NUMBER (10),
uom1 VARCHAR2 (5),
pri1 VARCHAR2 (10),
amo1 VARCHAR2 (10),
total VARCHAR2 (10) );

I want in_inv (table BILL)and in_line (table ITEM) can generate AUTO INCREMENT value.

I have try this but get compile error msg.

CREATE TABLE bill
(in_inv NUMBER PRIMARY KEY,
bill VARCHAR2 (512),
ship VARCHAR2 (512),
invdate DATE,
invno VARCHAR2 (12),
po VARCHAR2 (12),
dn VARCHAR2 (15),
cur VARCHAR2 (15),
pri VARCHAR2 (35),
term VARCHAR2 (30) );

CREATE SEQUENCE bill_seq
START WITH 1
INCREMENT BY 1
CACHE 1000;

CREATE OR REPLACE TRIGGER bill_preinsert
BEFORE INSERT ON bill
FOR EACH ROW
BEGIN
SELECT bill_seq.nextval
INTO :new in_inv
FROM dual;
END;


CREATE TABLE item
(in_line NUMBER PRIMARY KEY,
line NUMBER,
invno VARCHAR2 (12),
po1 VARCHAR2 (10),
stock1 VARCHAR2 (2000),
qty1 NUMBER (10),
uom1 VARCHAR2 (5),
pri1 VARCHAR2 (10),
amo1 VARCHAR2 (10),
total VARCHAR2 (10) );

CREATE SEQUENCE item_seq
START WITH 1
INCREMENT BY 1
CACHE 1000;

CREATE OR REPLACE TRIGGER item_preinsert
BEFORE INSERT ON item
FOR EACH ROW
BEGIN
SELECT item_seq.nextval
INTO :new in_line
FROM dual;
END;

Thanks in advance

  #2 (permalink)  
Old June 16th, 2004, 08:46 AM
Authorized User
 
Join Date: May 2004
Location: , , .
Posts: 28
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you tell us the error message your getting

  #3 (permalink)  
Old June 16th, 2004, 08:53 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It appears that there is a missing period between the :new and the column name in both triggers.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
auto increment primary key jcuga SQL Language 2 June 3rd, 2007 09:04 AM
Increment a primary key bvpsekhar MySQL 7 May 9th, 2007 07:09 AM
SQL query to find key when auto-increment SandyFeder SQL Server ASP 1 November 21st, 2005 10:49 AM
How to auto increment primary key method SQL Server 2000 5 May 24th, 2005 03:25 PM
auto gerate of primary key Abhinav_jain_mca SQL Server 2000 15 October 1st, 2004 05:51 AM



All times are GMT -4. The time now is 10:02 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.