Re: reset a sequence in a trigger

Werner Hofmann 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

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 Received on Thu May 19 2005 - 09:27:14 CDT