At 02:30 PM 10/05/2006, you wrote:

>Hi All,
>
>I have a set of cascade delete procedures which delete all records
>associated with clients if you delete the profile of a client from a
>People table within the database. The cascade delete will totally
>remove all details of all clients and their relevant transcripts from
>the database if you 'delete from People'. However, the generators that
>are used for the samples, people and transcriptions are not reset to
>0; they are of differing values of say (3,6 and 8). I want to update
>the cascade delete to set the generators to 0. How would I do this to
>a populated database ?
>(without creating a new database with the new strored procedure and
>then refilling the data)?
>
>Do the commands "Set Generator generator_name to value;' not work
>within a stored procedure?

Yes, DDL commands don't work in a stored procedure.

If you must do this extremely foolish thing, then call GEN_ID(),

e.g.

declare variable dingdong BigInt;

begin
....
dingdong := gen_id (somegenerator, (-1 * gen_id(somegenerator,0)));
end

But there is no guarantee that somegenerator will end up as 0 after
your transaction commits (another transaction could increment it) and
there is no way to restore the previous values if your transaction rolls back.

In short, don't fiddle with generators in your code other than to
fetch the current or next value.

./heLen