Table Triggers on Oracle

[gregglarkin]

I have a question from one of my developers:

"When working with JDE table triggers, at what point is the table/database actually updated ?

I have some event rules in the 'After record is updated' event, but when I query the table (F41021), I see the values have not been updated as yet. Shouldn't the new values be in the table in the 'After record is updated' event ? This is in E900 8.98.32 using the Oracle database."

When we work with SQL server, table triggers work fine. Now we are working with Oracle and things are not behaving as expected. The developer has search the knowledge jungle with no hits. I also had him place an SR with Oracle. I have a six pack bet that JDElist will come up with the answer faster. Ready? Set? Go!

- Gregg

 

[ice_cube210]

See if this note 648743.1 on the support site helps.

Since E1 table triggers are in the application layer , the actual behavior should be the same no matter what the back end database. Are they saying they see otherwise? Also is your SQL server scenario on the same App and tools release as the Oracle DB , I am guessing not ..? if thats the case it could be the App , tools release that is causing the different behavior and not the actual database

 

[Chan Rana]

Gregg,
If the transaction processing is ON then the update will not happen until the transaction is completed. There is one more event called After commit i think thats the best place to put the code since it will fire onlt after commit which gives you result after with new values.

Chan

 

[Chan Rana]

Gregg,
Ignore my earlier post since thats related to screen. Did you try regenerating triggers after moving to new DB?.

Chan

 

[gregglarkin]

Chan and Ice Cube

I forwarded your ideas over to the developer that had the question. I'll post his reply after he reviews your suggestions.

There could be some release / toolset / database engine issues here. We developed our triggers on XE and MS SQL. Now we are recreating the same logic for 9.0 and Oracle.

Previously, we had a test system on 8.12, then 9.0 on SQL. That has transitioned over to 9.0 and Oracle. Lots of changes to account for.

- Gregg

 

[Larry_Jones]

Gregg,

JDE/Oracle is (unfortunately) using more database transactions (BEGIN, Inserts,Updates,Deletes, COMMIT/ROLLBACK) in the application code than was present in ealier releases. I say unfortunately because several applications are now sometimes subject to "deadly embraces" or locked sessions as a result of either poor coding or just too complex code.

 

[Chan Rana]

Gregg,
Its is also important to know the source from where this event is triggered. After they incorporated XML etc. kernels behaves differently when called from Apps, UBE or BSSV etc. Better would be to generate logs and analyze.

Chan

 

[Rayxue]

Our SQL trigger are working fine, and I can't test Oracle DB trigger. But to figure out either Oracle never work or transfer from XE/MS SQL causing problem, you can try another table F56XYZ (never be defined as trigger) at Oracle. Common sense for me, trigger should work for Oracle.