At 10:02 AM 27-08-01 +0200, Rado wrote:

>Hi Don,
>
> > Using the standard trigger/generator method to create a unique primary key
> > value for a newly inserted record, is there a way to determine the primary
> > key/generator value of the record I have just inserted? I want to
> > insert the
> > new record using a stored proc. and then use the primary key's value to do
> > further processing within the same stored proc.
>
>If I understand it correctly you need something like:
>
>CREATE PROCEDURE GET_NEW_ACCOUNT_NO
>RETURNS
>(
> AVALUE INTEGER
>)
>AS
> BEGIN
> AVALUE = GEN_ID(GEN_ACCOUNT_NO, 0);
>END;
>
>Note that GEN_ID(GEN_FOO, 0) does not increase the
>value of GEN_FOO.

Rado, Don,

This is *not* a good idea. You have no way of knowing whether the current value of the generator is the same as the one generated for your new row.

The only certain way to get the generator value uses other than the immediate one of creating the primary key value is to bring the value into your application BEFORE you use it. When inserting your new row, pass the value you obtained earlier.

You may need to modify your BEFORE INSERT trigger to ensure you don't get a new value generated when you finally perform the COMMIT. That is, you must have your trigger check for a NULL input and fire the generator only in that case.

The deadlock isn't being caused by the generator - generators are not subject to transaction control and are unaffected by concurrency conflicts in the transactions will call them.

Here is your problem:

/* Finally delete all temporary items from the TMP_CASH table. */
DELETE FROM TMP_CASH
WHERE TERM_NO = :v_term_no;

SUSPEND; /* AND GET RID OF THIS */

All of the stuff in the stored proc is happening within the same transaction context. Because the transaction is already holding TMP_CASH in suspension pending the COMMIT of the rest of the stuff, it can't delete the rows yet.

Remove that code from this proce and instead send a quick DSQL across in a separate transaction to clear these rows once the processing is complete (committed or rolled back).

My other note - don't use SUSPEND in SPs other than those that return multiple-row datasets. If you want a "documentary device" to show readers that the procedure is finished, use EXIT.

rgds,
Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________