Question

Oracle 中创建一触发器，要求完成：往表插入数据时，用触发器来完成序列的插入。

如：
create table t_user(
u_id number ,
u_name varchar2(20),
u_pwd varchar2(20)
);
craete sequence user_seq start with 1 increment by 1;
在完成insert into t_user(u_name,u_pwd) values('','')时触发器自动增长序列的值，相当于：insert into t_user values(user_seq.nextval,'','');这个功能，求解。

Answer 1

CREATE OR REPLACE TRIGGER triggerName BEFORE INSERT ON table t_user FOR EACH ROW
when (new.u_id is null)
begin
select user_seq.nextval into:new.id from dual;
end;
一般情况下会直接在程序里去调用序列，因为数据量太大的话，对表逐行扫描的话会影响性能

Answer 2

我感觉应该等他插入后，然后根据用户名来修改U_id
CREATE OR REPLACE TRIGGER insert_tuser_before
after INSERT ON t_user
FOR EACH ROW
BEGIN
update t_user set u_id=:new.user_seq.nextval where u_name=:new.u_name;
END ;

Answer 3

CREATE OR REPLACE TRIGGER trg_uid
BEFORE INSERT
ON t_user
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
Select user_seq.nextval
into :new.u_id
From DUAL;
END ;
/

Answer 4

buzhidao