Sujit's suggestion is close. I agree with his first two steps. I've never tried an update like his step 3, so I'm not sure if that will work, or not. If it does, that's fine. If not, you will have to write a short PL\SQL block to fetch each row via a cursor, then do an update that uses TEST_SEQ.NEXTVAL. For step 4, you should write trigger like this:
create or replace trigger set_id before insert on [your_table]
for each row
begin
select TEST_SEQ.NEXTVAL into :new.id from dual;
end;
/
If that gives you errors, or doesn't work as intended, then this slightly-more complex version should work:
create or replace trigger set_id before insert on [your_table]
for each row
declare
cursor c1 is select TEST_SEQ.NEXTVAL from dual;
dummy number;
begin
open c1;
fetch c1 into dummy;
close c1;
:new.id := dummy;
end;
/
Main Topics
Browse All Topics
by: sujit_kumarPosted on 2007-07-11 at 13:39:40ID: 19466568
what you can do is,
1. create an sequence.
CREATE SEQUENCE TEST_SEQ START WITH 1 INCREMENT BY 1;
2. Add that column (ID) in your table.
ALTER TABLE TEST_TABLE ADD COLUMN (ID NUMBER);
3. Do a one-time update for the column to populate the ID.
UPDATE TEST_TABLE SET ID = ROWNUM;
4. Then, whenever you INSERT any row into this table insert Sequence's NEXTVAL (TEST_SEQ.NEXTVAL) into the ID column.
INSERT INTO TEST_TAB (ID, .....) VALUES (TEST_SEQ.NEXTVAL, ....);