oracle 通过触发器和sequence 实现字段自增

来源：互联网发布：anaconda与python 编辑：程序博客网时间：2024/04/30 22:03

CREATE TABLE example(
             ID Number(4) NOT NULL PRIMARY KEY,
             NAME VARCHAR(25),
             PHONE VARCHAR(10),
             ADDRESS VARCHAR(50) );

create sequence foo_seq;
drop trigger first_trigger;commit;
CREATE or replace TRIGGER first_trigger BEFORE
          INSERT ON example FOR EACH ROW
      begin
          select foo_seq.nextval into :new.id from dual;
       end;

insert into example(NAME, Phone, address) values('yg', '3234', 'xinhua');

插入数据测试成功，如果不成功，可能是commit的问题

注意，下面这个触发器会报错：

CREATE or replace TRIGGER first_trigger BEFORE
          INSERT ON example FOR EACH ROW
      begin
          select foo_seq.nextval into: new.id from dual
       end;

因为第四行into必须与：分开

一个sequence示例

create sequence send_sequence
increment by 1
start with 1
nomaxvalue
nocycle
nocache;
commit;

查询某表的触发器

select * from user_triggers where table_name = 'NMS_CAPA_TYPE';