|
Home > Archive > FoxPro database connector > August 2005 > Problem with Update trigger and transaction
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Problem with Update trigger and transaction
|
|
|
| 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?
| |
|
| 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
"Pat" wrote:
> 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?
| |
|
| .... and be aware that some commands and functions are NOT allowed inside a
transaction, hence the error. Please check the help file.
hth
Mark
"Pat" wrote:
> 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?
|
|
|
|
|