Re: reset a sequence in a trigger

From: Lewis C <lewisc_at_excite.com>
Date: Thu, 19 May 2005 22:11:51 GMT
Message-ID: <6k3q819hpglftvnrihauoj5gbh4jdv80jb@4ax.com>

On Thu, 19 May 2005 15:02:18 +0200, Werner Hofmann <superomega_at_t-online.de> wrote:

>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

Received on Thu May 19 2005 - 17:11:51 CDT