reset a sequence in a trigger

From: Werner Hofmann <superomega_at_t-online.de>
Date: Thu, 19 May 2005 15:02:18 +0200
Message-ID: <d6i2p1$am8$1@svr7.m-online.net>

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 Received on Thu May 19 2005 - 08:02:18 CDT