-
使用trigger实现主键自增
----创建表
create table tb_user
(
ID NUMBER PRIMARY KEY,
user_name varchar2(20),
user_address varchar2(30)
);
-----创建tb_user_seq序列
CREATE SEQUENCE tb_user_seq MINVALUE 1 MAXVALUE 999
INCREMENT BY 1
START WITH 1;
------创建触发器,在insert数据前获取next_id
CREATE OR REPLACE TRIGGER tb_user_tri
before
insert on tb_user
for each row
declare
next_id NUMBER;
begin
SELECT tb_user_seq .NEXTVAL INTO next_id FROM dual;
:new.ID :=next_id;
end;
—创建表user_tb
create table user_tb
(
userID integer PRIMARY key,
user_name VARCHAR2(20),
sex VARCHAR2(10)
);
----创建序列
CREATE SEQUENCE user_tb_seq
MINVALUE 1 maxvalue 9999 --序列从1–到9999
INCREMENT by 1
start WITH 1;
----创建触发器
CREATE or REPLACE TRIGGER user_tb_tri
before
insert on user_tb
for EACH ROW
BEGIN
SELECT user_tb_seq.nextval into:new.userID from dual;
end;
—测试插入数据
insert into USER_TB (USER_NAME,SEX) VALUES(‘李红’,‘女’);
insert into USER_TB(USER_NAME,SEX)VALUES(‘大伟’,‘男’);
备注:
create or replace TRIGGER 触发器名字
BEFORE INSERT ON 表名
for each row
DECLARE
next_id NUMBER;
BEGIN -------触发语句
select 序列名.Nextval into next_id from dual;
:new.id:=next_id;
END;