Question

oracle创建自增序列号触发器

哈！各位大虾，帮我，我想要这样的触发效果：
表结构例如是这样：create table TB(N char(10) primary key,M char(10))
当我首次将数据插入列M的时候，N自动生成0001
再插入列M第二行的时候，N自动生成0002
以此类推，每插入M时，N就自动递增1，前面的0不能省略，同时还有个要求，该触发器不能依赖序列。
请帮帮我。

Answer 1

create or replace trigger tri_input_no
before insert on mytb
for each row
begin
declare

cursor c is

select 's' from mytb;
rec varchar2(6);
begin

open c;

fetch c into rec;

if c%rowcount = 0 then

select '0001' into :new.mname from dual;

else

select (select lpad(substr(max(mname), 2) + 1, 4, '0') from mytb)

into :new.mname

from dual;

end if;
end;
end;

刚写出来，测试过的，把表和字段改下就行了

按你的表：

create or replace trigger tri_input_no_test
before insert on TB
for each row
begin
declare
cursor c is
select 's' from TB;
rec varchar2(6);
begin
open c;
fetch c into rec;
if c%rowcount = 0 then
select '0001' into :new.N from dual;
else
select (select lpad(substr(max(N), 2) + 1, 4, '0') from TB)
into :new.N
from dual;
end if;
end;
end;