At 10:47 AM 27-08-01 +0200, you wrote:

>Thank you Helen,
>
>At the moment the trigger looks like this:
>
>CREATE TRIGGER "SET_CASH_INV_NO" FOR "CASH_SALES"
>ACTIVE BEFORE INSERT POSITION 0
>AS
>BEGIN
> IF (NEW.INVOICE_NO IS NULL) THEN
> NEW.INVOICE_NO = GEN_ID(CASH_INV_NO_GEN, 1);
>END
> ^
>
>and in the stored proc I do this:
> /* First we retrieve the new invoice no. and then create a new cash
>transaction */
> v_invoice_no = GEN_ID(CASH_INV_NO_GEN, 1);
> INSERT INTO CASH_SALES
> (INVOICE_NO, CUST_ID, TRANS_DATE, TRANS_TIME, TERM_NO, VAT, VAT_TOTAL,
>GND_TOTAL)
> VALUES
> (:v_invoice_no, :p_cust_id, 'NOW', 'NOW', :p_term_no, :p_vat,
>:p_vat_total, :p_gnd_total);
>
>As you can see, I'm using the v_invoice_no variable's value (retrieved from
>the generator) to insert the new record. Is this the correct way?

It looks pretty good to me. There is no way it can cause a violation here.

rgds
Helen

>Best Regards,
>Don Schoeman
>
>
>
>-----Original Message-----
>From: Helen Borrie [mailto:helebor@...]
>Sent: 27 August 2001 10:20
>To: ib-support@yahoogroups.com
>Subject: RE: [ib-support] Determining generator's current value
>
>
>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
>_______________________________________________________
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

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