How do I fire trigger with sequnce value condition? [message #302244] |
Mon, 25 February 2008 00:54  |
ora_pbp
Messages: 3 Registered: February 2008
|
Junior Member |
|
|
Hi all,
I have a sequence run from 1000 to 9999 create by
CREATE SEQUENCE testseq
MAXVALUE 9999
MINVALUE 1000
START WITH 1000
ORDER
NOCACHE
CYCLE
INCREMENT BY 1;
commit;
and one test table like this
SQL> select * from tdir;
VDIR
----------
20000
Everytime testseq reach maxvalue(9999), i want to increase vdir by 1. How can i do this? I try searching to do this with trigger but most examples, trigger condition are about insert or update on table. Can I fire trigger when sequence reach maximum value?
|
|
|
|
Re: How do I fire trigger with sequnce value condition? [message #302251 is a reply to message #302244] |
Mon, 25 February 2008 01:06   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Which program-unit uses your sequence?
One thing I can think of is creating a function that returns the sequences nextval.
Make this function an autonomous transaction and update (plus commit) your table whenever the sequence recycles.
I agree with Michel though that this is not ideal.
Maybe if you could explain why you think you need to keep track of the number of cycles, we could come up with a better way.
[Updated on: Mon, 25 February 2008 01:07] Report message to a moderator
|
|
|
Re: How do I fire trigger with sequnce value condition? [message #302254 is a reply to message #302246] |
Mon, 25 February 2008 01:13   |
ora_pbp
Messages: 3 Registered: February 2008
|
Junior Member |
|
|
Thanks, Michel. but the sequence need to cycle e.g.
in detail my application will create file name base on value from sequence then put it in folder name base on value in tdir.
the result will be like this
folder 20000 will have file 1000,1001,...9999
folder 20001 will have file 1000,1001,...9999
...
|
|
|
Re: How do I fire trigger with sequnce value condition? [message #302256 is a reply to message #302254] |
Mon, 25 February 2008 01:22   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Can't you use something like this:
SQL> select trunc(rownum/100), mod(rownum, 100)
2 from (select 1
3 from dual
4 connect by level <= 200
5 );
TRUNC(ROWNUM/100) MOD(ROWNUM,100)
----------------- ---------------
0 1
0 2
0 3
0 4
0 5
0 6
0 7
0 8
0 9
0 10
0 11
<snip>
0 94
0 95
0 96
0 97
0 98
0 99
1 0
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
1 13
1 14
<snip>
1 96
1 97
1 98
1 99
2 0
200 rows selected.
|
|
|
|
|