FAQ de Base de Datos

[sir_joshua]

BD: ORACLE

PREGUNTA: COMO CREAR UN CAMPO AUTO NUMERICO..?

RESPUESTA:

create table tbl_foo(
id number primary key,
txt varchar2(20)
);

create sequence seq_foo;

create trigger trg_foo
before insert on tbl_foo
for each row
begin
select seq_foo.nextval into :new.id from dual;
end;
/

insert into tbl_foo (txt) values('bar');
insert into tbl_foo (txt) values('baz');
insert into tbl_foo (txt) values('qqq');

select * from tbl_foo;
ID TXT
---------- --------------------
1 bar
2 baz
3 qqq


Ya esta! Ahora, trata de especificar un id arbitrariamente:

insert into tbl_foo (id, txt) values(100, '###');
insert into tbl_foo (id, txt) values(200, '???');
insert into tbl_foo (id, txt) values(300, '!!!');

No funciona, porke es sobre escrito por el trigger!

select * from tbl_foo;
ID TXT
---------- ----------------------------------------------------------------------------------------------------
1 bar
2 baz
3 qqq
4 ###
5 ???
6 !!!

Cambiando el trigger ...

create or replace trigger trg_foo
before insert on tbl_foo
for each row
begin
if :new.id is null then
select seq_foo.nextval into :new.id from dual;
end if;
end;
/

.... Trata otra ves:

insert into tbl_foo (id, txt) values(111, 'This');
insert into tbl_foo (id, txt) values(222, 'should');
insert into tbl_foo (id, txt) values(333, 'work');

De verdad funciona...

select * from tbl_foo;

ID TXT
---------- --------------------------------------
1 bar
2 baz
3 qqq
4 ###
5 ???
6 !!!
111 This
222 should
333 work