要用到触发器
1:此处触发器结构:
create or replace tirrger tirrger_name
before insert
on table_name
for each row
----------------------------
declare
begin
null;
end;
2:建表
create table c
(
id number primary key,
name varchar2(20)
)
3:建触发器
CREATE OR REPLACE TRIGGER tr_613
BEFORE INSERT
ON c
FOR EACH ROW
DECLARE
BEGIN
SELECT seq_1.NEXTVAL INTO :NEW.id FROM dual;
END;
如果要在id前加上日期以便日后好查询
可以把begin end中的select替换为:
SELECT (to_char(SYSDATE,'yyyymmdd') || (seq_1.NEXTVAL)) INTO :NEW.id FROM dual;
4:测试
INSERT INTO c(NAME) VALUES('a');
INSERT INTO c(NAME) VALUES(123);
INSERT INTO c(NAME) VALUES('a');
INSERT INTO c(NAME) VALUES(a); --故意出错 在insert 检验是否是连续的
INSERT INTO c(NAME) VALUES('a');
COMMIT;
SELECT *FROM c;
结果:
ID NAME
1 a
2 123
3 a
4 a
成功!