| Primary Key Random Number [message #370175] |
Thu, 28 December 2000 09:19  |
Ralphie
Messages: 14
Registered: October 2000
|
Junior Member |
|
|
If I want a primary key in Oracle to be a randomly generated number, how would I create a PL/SQL trigger to generate a random number for this Primary Key column?
Thank you in advance,
Chris
|
|
|
|
|
|
| Re: Primary Key Number [message #370178 is a reply to message #370176] |
Thu, 28 December 2000 11:03  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Do u really need a trigger for that?
I would first create a sequence "sequence_name" and then a procedure.
CREATE OR REPLACE PROCEDURE LOAD_your_table IS
BEGIN
Insert into your_table
(value1,
value2,
value3)
SELECT
sequence_name.NEXTVAL
, value2,
, value3
FROM table_name;
END LOAD_your_table;
|
|
|
|
| Re: Primary Key Random Number [message #370179 is a reply to message #370176] |
Thu, 28 December 2000 12:15 |
Salman Khan
Messages: 51
Registered: September 2000
|
Member |
|
|
Hi,
I would suggest you to create a sequence first and then if you want you can create a function to call next sequence number anywhere in your program.
e.g like this
Step 1.
CREATE SEQUENCE seq_num INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 MINVALUE 1;
Step 2.
CREATE OR REPLACE FUNCTION GET_SEQNO
return number
is
seq_no number;
begin
select seq_num.nextval into seq_no from dual;
return seq_no;
end ;
Step 3.
insert into abcd values (column1,column2,column3,seq_num.nextval);
Bye
|
|
|
|
| Re: Primary Key Number [message #370190 is a reply to message #370178] |
Fri, 29 December 2000 13:07 |
Ralphie
Messages: 14
Registered: October 2000
|
Junior Member |
|
|
|
I would prefer a trigger because if I had a trigger in place, I would not need to worry about who places what information into the database table through any application, whenever a new row is created, the Primary Key would have the next number generated and entered for the row.
|
|
|
|
| Re: Primary Key Number [message #370191 is a reply to message #370190] |
Fri, 29 December 2000 14:57 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
In this case you do the following:
1. Create sequence:
CREATE SEQUENCE MY_SEQ INCREMENT BY 1 START WITH 1;
2. Create the following type of trigger;
CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
DECLARE
v_seq_no NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL
INTO v_seq_no FROM dual;
:NEW.column_name:= v_seq_nu;
END;
That will certanly work.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
