reset a sequence in a trigger

#1

Hallo,

is there a solution to reset a sequence inside a trigger?

I wanted to use this procedure:

####################
CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as
curr_val INTEGER;
BEGIN
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual'
INTO curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by
-'||curr_val;
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO
curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1';
END reset_sequence;
/
#####################

but the when I call the 'before insert' or 'after delete' trigger I get
the message:

--> Commit is not allowed inside a trigger.

Thank you, Werner

#2

altering a sequence is a DDL statement. Oracle always precedes any DDL
with an implicit commit. And yes, commits do not belong in a trigger.

I'd try to move altering the sequence into a storee procedure with
AUTONOMOUS TRANSACTION.

Untested.

#3

Werner Hofmann wrote:

Quote:

Hallo,

is there a solution to reset a sequence inside a trigger?

I wanted to use this procedure:

####################
CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2)
as
curr_val INTEGER;
BEGIN
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE
0';
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual'

INTO curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by
-'||curr_val;
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual'
INTO
curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by
1';
END reset_sequence;
/
#####################

but the when I call the 'before insert' or 'after delete' trigger I
get
the message:

--> Commit is not allowed inside a trigger.

Thank you, Werner

DDL's (alter ...) automatically issue a commit before execution and you
cannot commit inside a trigger... so that is why you get this error.

1) Lookup usage for pragma autonomous_transaction at
http://tahiti.oracle.com
2) What's the point of using a sequence when you are going to reset it
everytime you insert/delete a row?

Regards
/Rauf

#4

Quote:

1) Lookup usage for pragma autonomous_transaction at
http://tahiti.oracle.com
2) What's the point of using a sequence when you are going to reset it
everytime you insert/delete a row?

Regards
/Rauf

Hallo Rauf,

I don't reset the sequence every time the trigger is called, but I have some
code, which is automatically cleaning the content of the table. And in the
case the cleaner has deleted all rows, then I wanted to reset the trigger.

In the firebird database I can reset the Generator(sequence) - no problem,
but oracle won't like my idea. So, if 1) is not my solution, in last
consequence I habe to reset the sequence outside the trigger anywhere in
the code.

Thank you,
Werner

#5

On Thu, 19 May 2005 15:02:18 +0200, Werner Hofmann
<superomega (AT) t-online (DOT) de> wrote:

Quote:

Hallo,

is there a solution to reset a sequence inside a trigger?

I wanted to use this procedure:

####################
CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as
curr_val INTEGER;
BEGIN
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual'
INTO curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by
-'||curr_val;
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO
curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1';
END reset_sequence;
/
#####################

but the when I call the 'before insert' or 'after delete' trigger I get
the message:

--> Commit is not allowed inside a trigger.

Thank you, Werner

Stupid question maybe but why reset it? A sequence is a large number.
Are you try to save numbers? You can set a max value and then cycle
it. Resetting a sequence just seems like you're going about solving
the wrong problem.

Thanks,

Lewis

-----------------------------------------------------------
Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle
http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database
http://www.suite101.com/welcome.cfm/oracle

Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------

#6

Quote:

Stupid question maybe but why reset it? A sequence is a large number.
Are you try to save numbers? You can set a max value and then cycle
it. Resetting a sequence just seems like you're going about solving
the wrong problem.

Thanks,

Lewis

In this table I have a lot of transactions, and it can be, that the ID which
is the primary key can overrun after some time (some years). I can not use
another primary key, because this would be with a VARCHAR2 column. And so I
wantet to be save, that the oldest entry have the smallest ID for sorting.

Werner

#7

"Werner Hofmann" <superomega (AT) t-online (DOT) de> wrote

Quote:

Stupid question maybe but why reset it? A sequence is a large number.
Are you try to save numbers? You can set a max value and then cycle
it. Resetting a sequence just seems like you're going about solving
the wrong problem.

Thanks,

Lewis

In this table I have a lot of transactions, and it can be, that the ID
which
is the primary key can overrun after some time (some years). I can not use
another primary key, because this would be with a VARCHAR2 column. And so
I
wantet to be save, that the oldest entry have the smallest ID for sorting.

Werner
Look at global temporary tables. This will probably do what you want very

nicely and not reset the sequence. I could see some major concurrency
issues with resetting the sequence dynamically like that.
Jim

#8

Hi Werner,

Try using the below trigger . This will not give the Error . DDL
Commands have implicit commit attached to it . Use of Autonomous
Transaction feature will eliminate the error message. True in 8i and
above .

####################
CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2)
as
curr_val INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';

EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM
dual'
INTO curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment
by
-'||curr_val;
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM
dual' INTO
curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment
by 1';
END reset_sequence;
/
#####################

Cheers
Abhishek

#9

Quote:

1) Lookup usage for pragma autonomous_transaction at
http://tahiti.oracle.com
2) What's the point of using a sequence when you are going to reset it
everytime you insert/delete a row?

Regards
/Rauf

Hallo Rauf,

PRAGMA AUTONOMOUS_TRANSACTION;
is working fine.

Thank you,
Werner

#10

Werner Hofmann wrote:

Quote:

1) Lookup usage for pragma autonomous_transaction at
http://tahiti.oracle.com
2) What's the point of using a sequence when you are going to reset it
everytime you insert/delete a row?

Regards
/Rauf

Hallo Rauf,

I don't reset the sequence every time the trigger is called, but I have some
code, which is automatically cleaning the content of the table. And in the
case the cleaner has deleted all rows, then I wanted to reset the trigger.

In the firebird database I can reset the Generator(sequence) - no problem,
but oracle won't like my idea. So, if 1) is not my solution, in last
consequence I habe to reset the sequence outside the trigger anywhere in
the code.

Thank you,
Werner
You don't reset the trigger, you reset the sequence.

Why can't you do it in your code that does the automatic cleaning??

Ed

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost