Home » SQL & PL/SQL » SQL & PL/SQL » How do I fire trigger with sequnce value condition?
How do I fire trigger with sequnce value condition? [message #302244] Mon, 25 February 2008 00:54 Go to next message
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 #302246 is a reply to message #302244] Mon, 25 February 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not directly.
But you can use "nocycle" and use a trigger "on servererror" and trap error 8004 ("sequence exceeds MAXVALUE and cannot be instantiated") and do what you want.

Of course, this may not be the best way to achieve your business need.

Regards
Michel

[Updated on: Mon, 25 February 2008 00:59]

Report message to a moderator

Re: How do I fire trigger with sequnce value condition? [message #302251 is a reply to message #302244] Mon, 25 February 2008 01:06 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: How do I fire trigger with sequnce value condition? [message #302259 is a reply to message #302254] Mon, 25 February 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but the sequence need to cycle e.g.

Yes and this what you manually do in the trigger.
But of course, this is not safe in case of concurrent accesses.
Investigate Frank way to use part of sequence for your folder and other part for your base name.

Regards
Michel

[Updated on: Mon, 25 February 2008 01:29]

Report message to a moderator

Re: How do I fire trigger with sequnce value condition? [message #302285 is a reply to message #302244] Mon, 25 February 2008 03:22 Go to previous message
ora_pbp
Messages: 3
Registered: February 2008
Junior Member
Thanks Frank. I will try your trick.
Previous Topic: ORA-02298: cannot validate (PL_REM_FK_ES_EMP) - parent keys not found
Next Topic: help needed
Goto Forum:
  


Current Time: Wed Dec 07 14:18:23 CST 2016

Total time taken to generate the page: 0.11545 seconds