drop table username;
CREATE TABLE username (
userId number(10) primary key,
username varchar(20) not null,
password varchar(20) not null);drop sequence username_seq;
create sequence username_seq minvalue 1 maxvalue 99999999 increment by 1 start with 1;
create or replace trigger username_tri
before insert or delete on username
for each row
begin select username_seq.nextval into :new.userId from dual;
end;
/
insert into username (username,password) values ('s','s');
insert into username (username,password) values ('ss','ss');
insert into username (username,password) values ('sss','sss');
insert into username (username,password) values ('ssss','ssss');
insert into username (username,password) values ('sssss','sssss');commit;
SQL> delete from username where userId=6;
delete from username where userId=6
*
第 1 行出现错误:
ORA-04084: 无法更改此触发器类型的 NEW 值
ORA-06512: 在 "SCOTT.USERNAME_TRI", line 1
ORA-04088: 触发器 'SCOTT.USERNAME_TRI' 执行过程中出错只能增加,不能删除(自减)。 请教,谢谢
最后能提供点资料更好。
CREATE TABLE username (
userId number(10) primary key,
username varchar(20) not null,
password varchar(20) not null);drop sequence username_seq;
create sequence username_seq minvalue 1 maxvalue 99999999 increment by 1 start with 1;
create or replace trigger username_tri
before insert or delete on username
for each row
begin select username_seq.nextval into :new.userId from dual;
end;
/
insert into username (username,password) values ('s','s');
insert into username (username,password) values ('ss','ss');
insert into username (username,password) values ('sss','sss');
insert into username (username,password) values ('ssss','ssss');
insert into username (username,password) values ('sssss','sssss');commit;
SQL> delete from username where userId=6;
delete from username where userId=6
*
第 1 行出现错误:
ORA-04084: 无法更改此触发器类型的 NEW 值
ORA-06512: 在 "SCOTT.USERNAME_TRI", line 1
ORA-04088: 触发器 'SCOTT.USERNAME_TRI' 执行过程中出错只能增加,不能删除(自减)。 请教,谢谢
最后能提供点资料更好。
在说sequence是无法往回递减的,所以你的自减实现不了
before insert on username
for each row
begin select username_seq.nextval into :new.userId from dual;
end;
/就可以了,delete的时候不需要让sequence减小,想一下如果可以让sequence往回走的话那还怎么保证生成唯一的值呢
我晒下思路吧
新建一个表increaseid,其中只有一个字段iid,类型int,先插入一个1.create or replace trigger username_tri
before insert or delete on username
for each row
idd number;
begin
if deleting then
select iid into idd from increaseid where iid= :old.userId ;
update increaseid set iid=iid-1 where iid>idd;
update username set userId =userId-1 where userId >idd;
end if;
if inserting then
select max(iid) into :new.userId from increaseid;
end if;
end;
本人手打的可能会出错,自己试试吧,应该行