oracle创建自增序列号触发器

oracle创建自增序列号触发器

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