• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2663
  • Last Modified:

Default nextval(sequence)

Hi I am want to alter primary key column to Default nextval(sequence)
I am getting fallowing error

ALTER TABLE emp
MODIFY(ID  DEFAULT nextval(SEQ_EMPNO))
ORA-00984: column not allowed here

Can you please tell me how to alter table with default value with squence

Thanks
0
basirana
Asked:
basirana
1 Solution
 
gvsbnarayanaCommented:
Hi,
   This is not possible with default clause.
You will need to create a before insert trigger to accomplish this.
HTH
Regards,
Badri.
0
 
johnsoneSenior Oracle DBACommented:
create trigger emp_ins
on emp
before insert
for each row
begin
  select seq_empno.nextval
    into :new.id
    from dual;
end;
/
0
 
jwahlCommented:
CREATE OR REPLACE TRIGGER EMP_IBR
 BEFORE INSERT
 ON EMP
 FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
       SELECT seq_empno.nextval
       INTO   :NEW.id
       FROM   dual;
    END IF;
END;
/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now