| Automatically assign the ID number to next row [message #1745] |
Sun, 26 May 2002 04:47  |
Max
Messages: 18
Registered: May 2002
|
Junior Member |
|
|
This is my table structure
create table passenger
(pnr_no char(7),
pnr_name char(20) NOT NULL,
pnr_street char(20),
pnr_suburb char(20),
pnr_postcode char(4)
CONSTRAINT passenger_pk PRIMARY KEY (pnr_no));
and this is my trigger
create trigger passenger_number
BEFORE INSERT ON PASSENGER
FOR EACH
ROW
BEGIN
SELECT PNR_NO.NEXTVAL
INTO: NEW.PNR_NO
FROM PASSENGER
END passenger_number;
/
I just want to automatically to assign the passenger id, but i can not do that. Can you fix that to me please
Thanks advance.
|
|
|
|
|
|
| Re: Automatically assign the ID number to next row [message #1748 is a reply to message #1745] |
Sun, 26 May 2002 09:57  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
/*
your syntax is wrong.
you cannot select a sequence.next value from a database table. u need to use a dual table.
and u need a sequence PNR_NO.
i have created it for here.
*/
SQL> ed
Wrote file afiedt.buf
1 create table passenger
2 (pnr_no char(7) PRIMARY KEY,
3 pnr_name char(20) NOT NULL,
4 pnr_street char(20),
5 pnr_suburb char(20),
6* pnr_postcode char(4))
7 /
Table created.
SQL> create sequence pnr_no start with 1 increment by 1;
Sequence created.
SQL> ed
Wrote file afiedt.buf
1 create trigger passenger_number
2 BEFORE INSERT ON PASSENGER
3 FOR EACH
4 ROW
5 BEGIN
6 SELECT PNR_NO.NEXTVAL
7 INTO :NEW.PNR_NO
8 FROM dual;
9* END passenger_number;
SQL> /
Trigger created.
SQL> insert into passenger values (null,'nag','ss','ss','ss');
1 row created.
SQL> select * from passenger;
PNR_NO PNR_NAME PNR_STREET PNR_SUBURB PNR_
------- -------------------- -------------------- -------------------- ----
1 nag ss ss ss
SQL> insert into passenger values (null,'nag','ss','ss','ss');
1 row created.
SQL> /
1 row created.
SQL> select * from passenger;
PNR_NO PNR_NAME PNR_STREET PNR_SUBURB PNR_
------- -------------------- -------------------- -------------------- ----
1 nag ss ss ss
2 nag ss ss ss
3 nag ss ss ss
SQL> insert into passenger values (7,'nag','ss','ss','ss');
1 row created.
SQL> select * from passenger;
PNR_NO PNR_NAME PNR_STREET PNR_SUBURB PNR_
------- -------------------- -------------------- -------------------- ----
1 nag ss ss ss
2 nag ss ss ss
3 nag ss ss ss
4 nag ss ss ss
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
