--- In firebird-support@yahoogroups.com, "Gaurav Sood"
<sood.gaurav@...> 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'.

You have just reinvented the wheel.

ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_1 FOREIGN KEY
(PEOPLEID) REFERENCES PEOPLE (ID) ON UPDATE CASCADE ON DELETE CASCADE;

Would have achieved the same thing with a lot less work.

> 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)?

You don't do this. You are not going to run out of 64 bit integers
before the human race becomes extinct. Once a generator (sequence)
value has been used, you never decrement. Doing so will cause real
problems if you have more than a single transaction executing at a
time.

Generator A = 1;

Tra1: Increment Generator A ( = 2)
Tra1: Does some things
Tra2: Increment Generator A (= 3)

<-- a bit later -->

Tra2: Insert a record in the table
Tra1: I might remove all the records I can see from the table
Tra1: I might just reset the generator
Tra1: commits
Tra2: commits

So now you have Generator A = 0 thanks to Tra1 but a single record in
the table (ID 3), which will cause problems when the generator next
dishes out ID 3.

>
> Do the commands "Set Generator generator_name to value;' not work
> within a stored procedure?

If you only have a single transaction ever (and you must be 100%
guaranteed, no chance of multiple simultaneous transactions), you can
increment the generator by (-1 * [current generator value]) to reset
it. I strongly advise you do not do this except for internal test
databases because of the issues I have highlighted.

Adam