没有问题啊SQL> CREATE OR REPLACE TRIGGER testname
2 before insert
3 on tb for each row
4 DECLARE
5 nfolderno number(8);
6 newfolderno varchar2(10);
7 BEGIN
8 SELECT nvl(MAX(id),0) into newfolderno FROM tb;
9 nfolderno:= TO_NUMBER(newfolderno);
10 nfolderno:=nfolderno+1;
11 newfolderno:=TO_CHAR(nfolderno,'00000000');
12 :new.id:=newfolderno;
13 END;
14 /触发器已创建
SQL> insert into tb values(10);已创建 1 行。SQL> insert into tb values(10);已创建 1 行。SQL> select * from tb;ID
--------------------
00000001
00000002SQL> insert into tb values(10);已创建 1 行。SQL> insert into tb values(10);已创建 1 行。SQL> select * from tb;ID
--------------------
00000001
00000002
00000003
00000004SQL>
估计是你的数据有问题,
你执行SELECT MAX(folder_no) FROM test; 看看是数字吗?
2 before insert
3 on tb for each row
4 DECLARE
5 nfolderno number(8);
6 newfolderno varchar2(10);
7 BEGIN
8 SELECT nvl(MAX(id),0) into newfolderno FROM tb;
9 nfolderno:= TO_NUMBER(newfolderno);
10 nfolderno:=nfolderno+1;
11 newfolderno:=TO_CHAR(nfolderno,'00000000');
12 :new.id:=newfolderno;
13 END;
14 /触发器已创建
SQL> insert into tb values(10);已创建 1 行。SQL> insert into tb values(10);已创建 1 行。SQL> select * from tb;ID
--------------------
00000001
00000002SQL> insert into tb values(10);已创建 1 行。SQL> insert into tb values(10);已创建 1 行。SQL> select * from tb;ID
--------------------
00000001
00000002
00000003
00000004SQL>
估计是你的数据有问题,
你执行SELECT MAX(folder_no) FROM test; 看看是数字吗?
newfolderno:=LTRIM(TO_CHAR(nfolderno,'00000000'));