在oraclek中,为了方便我常常用触发器及序列结合起来实现,下面就是一个示例,希望对兄弟们有帮助 先建表、再建序列、然后是触发器,最后测试============================================= --为主键指定序列编号 --2003-10-8 15:53 ============================================= conn scott/tiger@powermis drop table foo; create table foo( id number primary key, data varchar2(100)); create sequence foo_seq; create or replace trigger bifer_foo_id_pk before insert on foo for each row begin select foo_seq.nextval into :new.id from dual; end; / insert into foo(data) values('Chirstopher'); insert into foo(id,data) values(5,'Sean'); select * from foo;
CREATE SEQUENCE USERID INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE NOCACHE NOORDER;CREATE OR REPLACE TRIGGER USERADD BEFORE INSERT ON MEMBERRG FOR EACH ROW BEGIN SELECT LPAD(USERID.NEXTVAL,4,'0') INTO :NEW.NUM FROM DUAL; END;
无效呀
我要用它的话,Select语句有应该怎样写呢?
例如 aa
然后访问 aa.nextval就可以了。
先建表、再建序列、然后是触发器,最后测试=============================================
--为主键指定序列编号
--2003-10-8 15:53
============================================= conn scott/tiger@powermis drop table foo; create table foo(
id number primary key,
data varchar2(100)); create sequence foo_seq; create or replace trigger bifer_foo_id_pk
before insert
on foo
for each row
begin
select foo_seq.nextval into :new.id from dual;
end;
/ insert into foo(data)
values('Chirstopher'); insert into foo(id,data)
values(5,'Sean'); select * from foo;
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
NOCACHE NOORDER;CREATE OR REPLACE TRIGGER USERADD
BEFORE INSERT ON MEMBERRG
FOR EACH ROW
BEGIN
SELECT LPAD(USERID.NEXTVAL,4,'0') INTO :NEW.NUM FROM DUAL;
END;