if you use a sequence for increment, you can reset it when the year is changed. For example, store the year number in some place, when insert check the year number is the same as stored year, if not reset the sequence and updated the new year number.
See follow function:
Try this Function
CREATE OR REPLACE function restart_seq(SeqName IN varchar2) return Integer
as
PRAGMA AUTONOMOUS_TRANSACTION;
vs integer;
inc integer;
junk integer;
sqName varchar2(50);
begin
sqName := seqName||'.nextval';
execute immediate 'select '||sqName||' from dual' into vs;
inc := 0 - vs;
execute immediate 'alter sequence '||SeqName||' minvalue '|| inc;
execute immediate 'alter sequence '||SeqName||' increment by '||inc;
execute immediate 'select '||sqName||' from dual' into junk;
execute immediate 'alter sequence '||SeqName||' increment by 1';
RETURN junk;
end;
/
actually, i really suggest use guid instead of increment tag.