Problem with Update trigger and transaction

[Pat]

We are currently re-writting some code that wraps around a system that is Fox
5 that we do not own the code for ( eek ). The problem stems from an update
that needs to be done on an order table. The update merely indicates the qty
of pieces that have shipped on the packer we are creating.

We begin by using begin trans. The mentality is it all must go in, or it
all must rollback at one time. Parital updates to some tables and not others
would be a bad thing. After the begin trans we insert a packing slip header
record in its table. We then proceed to process all the detail records by
updating the order information and inserting the packer detail rows as we go
through. All of this is wrapped in the transaction we started in the
beginning. If one items fails we bailout and rollback.

The problem is when the order update occurs a trigger fires to record the
old values. This causes a 1598 error which basically is transactions out of
balance to occur. It would be nice if the trigger would fire when the commit
occurs basically when data is actually written to disk. The only way I see
to solve the issue is to not use transactions.

Anybody have a solution or some words of wisdom?

[Mark]

Hi Pat,
Transaction code goes similar to this

BEGIN TRANSACTION

lGoOn = .T.

SELECT header
IF NOT TABLEUPDATE()
lGoOn = .F.
ENDIF

IF lGoOn
SELECT detail

IF NOT TABLEUPDATE(.t.,.t.)
lGoOn = .F.
ENDIF
ENDIF

IF lGoOn
END TRANSACTION
ELSE
ROLLBACK
=TABLEREVERT(.f.,"header")
=TABLEREVERT(.t.,"detail")
ENDIF


HTH
Mark

Post by Pat
We are currently re-writting some code that wraps around a system that is Fox
5 that we do not own the code for ( eek ). The problem stems from an update
that needs to be done on an order table. The update merely indicates the qty
of pieces that have shipped on the packer we are creating.
We begin by using begin trans. The mentality is it all must go in, or it
all must rollback at one time. Parital updates to some tables and not others
would be a bad thing. After the begin trans we insert a packing slip header
record in its table. We then proceed to process all the detail records by
updating the order information and inserting the packer detail rows as we go
through. All of this is wrapped in the transaction we started in the
beginning. If one items fails we bailout and rollback.
The problem is when the order update occurs a trigger fires to record the
old values. This causes a 1598 error which basically is transactions out of
balance to occur. It would be nice if the trigger would fire when the commit
occurs basically when data is actually written to disk. The only way I see
to solve the issue is to not use transactions.
Anybody have a solution or some words of wisdom?

[Mark]

... and be aware that some commands and functions are NOT allowed inside a
transaction, hence the error. Please check the help file.

hth
Mark

Post by Pat
We are currently re-writting some code that wraps around a system that is Fox
5 that we do not own the code for ( eek ). The problem stems from an update
that needs to be done on an order table. The update merely indicates the qty
of pieces that have shipped on the packer we are creating.
We begin by using begin trans. The mentality is it all must go in, or it
all must rollback at one time. Parital updates to some tables and not others
would be a bad thing. After the begin trans we insert a packing slip header
record in its table. We then proceed to process all the detail records by
updating the order information and inserting the packer detail rows as we go
through. All of this is wrapped in the transaction we started in the
beginning. If one items fails we bailout and rollback.
The problem is when the order update occurs a trigger fires to record the
old values. This causes a 1598 error which basically is transactions out of
balance to occur. It would be nice if the trigger would fire when the commit
occurs basically when data is actually written to disk. The only way I see
to solve the issue is to not use transactions.
Anybody have a solution or some words of wisdom?