Home » SQL & PL/SQL » SQL & PL/SQL » Triggers, autonumbers and primary keys
Triggers, autonumbers and primary keys [message #438492] Sat, 09 January 2010 17:23 Go to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
Hi all, im new here.

Ive created a table and run it on application express. It works but my problem is the primary key. When i create an application with the table and try to enter a new row in the table i get an error message. The problem is the primary key column does not show up in the application so i cant enter a number into it. i tried creating a trigger but im not to sure if i have the correct syntax. any help would be much appreciated, thank you.

here is the script of the table and the trigger i tried to create.

ALTER TABLE studentdata
drop CONSTRAINT pk_studentdata;

DROP TABLE studentdata;

DROP SEQUENCE studentdata_StudentID_seq;

CREATE SEQUENCE studentdata_StudentID_seq START WITH 01;

CREATE TABLE studentdata
(StudentID NUMBER(38) NOT NULL,
MalNumber NUMBER (Cool NOT NULL,
Firstname VARCHAR2 (20) NOT NULL,
SecondName VARCHAR (20) NOT NULL,
Address VARCHAR2 (30) NOT NULL,
TelNumber NUMBER (12) NOT NULL,
Email VARCHAR2 (30) NOT NULL,
DOB DATE NOT NULL,
Nationality VARCHAR2 (18) NOT NULL,
Programme VARCHAR2 (20) NOT NULL,
CurrentYear VARCHAR2 (1) NOT NULL,
CONSTRAINT pk_studentdata PRIMARY KEY (StudentID));

INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111111','Alistar','Wilson','1 High Street',01698355155,'student1@uni.com','11-JAN-80','American','Computing',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111112','Colin','Kennedy','12 Main Road',01698291923,'student2@uni.com','12-JUN-83','British','Web Development',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111113','Fred','Barns','23 Old School Road',01698352590,'student3@uni.com','24-FEB-80','French','Databases',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111114','David','Wilson','6 Croft place',01412434567,'student4@uni.com','01-JAN-84','British','Computing',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111115','Steven','Jones','8 Miller Avenue',01236798654,'student5@uni.com','23-JUN-89','British','Web Development',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111116','Frank','Black','124 Castle Drive',01698888695,'student6@uni.com','28-FEB-67','French','Databases',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111117','James','White','8 Bank Road',01315694857,'student7@uni.com','01-JAN-80','American','Computing',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111118','Calvin','Harris','18 Lowe Street',016989547575,'student8@uni.com','12-JUN-83','British','Web Development',1);
INSERT INTO studentdata VALUES(studentdata_StudentID_seq.nextval,'11111119','Graham','Hill','129 Long Road',01698356789,'student9@uni.com','24-AUG-82','Irish','Databases',1);



CREATE OR REPLACE TRIGGER "studentdata"
BEFORE
insert on "studentdata"
for each row
begin
select "studentdata_SEQ".nextval into :NEW.StudentID from dual;

end;


Re: Triggers, autonumbers and primary keys [message #438494 is a reply to message #438492] Sat, 09 January 2010 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
does below work any better?

CREATE OR REPLACE TRIGGER studentdata
  BEFORE INSERT ON studentdata
  FOR EACH ROW
BEGIN
  SELECT studentdata_seq.nextval
  INTO   :NEW.studentid
  FROM   dual;
END;


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Triggers, autonumbers and primary keys [message #438495 is a reply to message #438492] Sat, 09 January 2010 18:01 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
Thank for the effort, it does not work though. I get the error:

15 0.07 CREATE OR REPLACE TRIGGER studentdata BEFORE INSERT ON stu ERROR at line 2: PL/SQL: ORA-02289: sequence does not exist
Re: Triggers, autonumbers and primary keys [message #438496 is a reply to message #438495] Sat, 09 January 2010 18:03 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So create it! The sequence, I mean.

[EDIT] Or, perhaps I should have said "Don't blindly copy Black Swan's code"

[Updated on: Sat, 09 January 2010 18:05]

Report message to a moderator

Re: Triggers, autonumbers and primary keys [message #438497 is a reply to message #438495] Sat, 09 January 2010 18:04 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It might help if the code actually use the SEQUENCE you created

CREATE SEQUENCE studentdata_StudentID_seq START WITH 01;
Re: Triggers, autonumbers and primary keys [message #438498 is a reply to message #438496] Sat, 09 January 2010 18:05 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
Littlefoot wrote on Sun, 10 January 2010 00:03
So create it! The sequence, I mean.



I dont understand what you mean. I'm just new to oracle and im learning at every opportunity. Could you be more specific.

Sorry to be a pain.
Re: Triggers, autonumbers and primary keys [message #438499 is a reply to message #438497] Sat, 09 January 2010 18:07 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
BlackSwan wrote on Sun, 10 January 2010 00:04
It might help if the code actually use the SEQUENCE you created

CREATE SEQUENCE studentdata_StudentID_seq START WITH 01;


I'm using that line in the INSERT INTO.
Re: Triggers, autonumbers and primary keys [message #438500 is a reply to message #438499] Sat, 09 January 2010 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>I'm using that line in the INSERT INTO.
NO, you are NOT!


studentdata_StudentID_seq

is different than

studentdata_seq

[Updated on: Sat, 09 January 2010 18:10]

Report message to a moderator

Re: Triggers, autonumbers and primary keys [message #438501 is a reply to message #438500] Sat, 09 January 2010 18:13 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
BlackSwan wrote on Sun, 10 January 2010 00:09
>I'm using that line in the INSERT INTO.
NO, you are NOT!


studentdata_StudentID_seq

is different than

studentdata_seq



lol at the newbie lol

sorry boys, i just realised your correct swan lol.... i was declairing a variable and not using it lol.

Ive been doing to much in not enough time and not taking it all in. Thanks for all your help.
Re: Triggers, autonumbers and primary keys [message #438514 is a reply to message #438501] Sun, 10 January 2010 01:42 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
maybe a bit overkill, but just to clarify:
1st create sequence
2nd create the trigger

You should not create the sequence in the trigger.
Previous Topic: Problem in creating procedure
Next Topic: Creating Large Cluster Table More Efficiently
Goto Forum:
  


Current Time: Mon Sep 26 14:33:35 CDT 2016

Total time taken to generate the page: 0.09740 seconds