Kbase P124986: A FOR EACH statement causes a write trigger to fire unexpectedly.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/18/2007 |
|
Status: Unverified
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
SYMPTOM(s):
A FOR EACH statement causes a write trigger to fire unexpectedly.
Write trigger fires unexpectedly.
A For each statement that is run inside a trigger for one buffer, causes another buffers WRITE trigger to fire.
A write trigger is fired within a write trigger for another buffer.
A joined buffer causes a WRITE trigger to fire.
FOR EACH <table1>, FIRST <table2> causes a WRITE trigger to fire.
For example, in the following code the FOR EACH in the MyDummy procedure (triggered by the order WRITE trigger) causes the MyCust buffer write trigger to fire.
/**************************************************************************/
DEFINE BUFFER MyCust FOR sports2000.customer.
DEFINE BUFFER MyOrder FOR sports2000.order.
ON WRITE OF order OVERRIDE DO:
RUN TestMe.
END.
ON WRITE OF customer OVERRIDE DO:
MESSAGE "WRITE-TRIGGER Customer !!!" VIEW-AS ALERT-BOX.
END.
trx:
DO TRANSACTION:
FIND FIRST MyCust WHERE MyCust.custnum = 1 EXCLUSIVE-LOCK NO-WAIT.
ASSIGN MyCust.city = "AnyWhere".
FIND FIRST MyOrder WHERE MyOrder.custnum = MyCust.custnum
EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
ASSIGN MyOrder.instructions = 'test only !'.
RELEASE MyOrder NO-ERROR.
RELEASE MyCust NO-ERROR.
UNDO trx, LEAVE trx.
END. /* TRX */
PROCEDURE testMe:
DEFINE BUFFER Bcust FOR sports2000.customer.
RUN MyDummy NO-ERROR.
END PROCEDURE.
PROCEDURE MyDummy:
DEFINE BUFFER Bcust FOR sports2000.customer.
DEFINE BUFFER Border FOR sports2000.order.
/* This FOR EACH causes the MyCust buffer Write trigger to fire */
FOR EACH Bcust NO-LOCK, FIRST Border WHERE Border.custnum = Bcust.custnum NO-LOCK:
END.
END PROCEDURE.
CAUSE:
This is expected behavior. Basically, when a FOR EACH/Query is to be executed with selection (WHERE clause), then Progress tries to force a write of all updated in memory records for that table to the server. This is so that the selection by the server will work correctly.
Therefore in this example, the order trigger has fired but the customer buffer has not been written. The FOR EACH Bcust, FIRST Border WHERE then forces the write trigger to fire and commit the changes so that the selection can be performed. This only happens with FOR EACH/Queries and does not happen with FIND.
FIX:
Modify the code to prevent this from happening by separating the updates into two separate transactions. Or use one of the following FOR EACH loops to prevent the WRITE trigger from firing:
FOR EACH Bcust NO-LOCK:
FIND FIRST Border WHERE Border.custnum = Bcust.custnum NO-LOCK NO-ERROR.
END.
FOR EACH Bcust NO-LOCK:
END.
FOR EACH Border WHERE Border.custnum = Mycust.custnum NO-LOCK:
END.
FOR EACH Border WHERE Border.custnum = Mycust.custnum NO-LOCK, FIRST Bcust WHERE Bcust.custnum = Border.custnum NO-LOCK:
END.