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?

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/