Table trigger's

[striker]

Table trigger\'s

Using : XE on Oracle DB

Q: Is there a way to Disable Table triggers?

Background:
We use a table trigger on the F4101 table. In the code it deletes a corresponding tag file record.
The issue we are trying to solve is, when the delete is performed on the Item Master Form, the corresponding tag file record is deleted, along with an extra short item '0' record in the tag file.

There are 2 Delete's being done for the same record. (which is a bug) First, at the Item Master Browse form (Delete Button) and next at the F4101 trigger level.

At the F4101 trigger level, they are using a plain delete on the Item Tag file, instead of a Fetch and Delete. I believe this is the cause of the '0' Item being deleted in the Tag table.

What is strange is, the same code is working in PY ie., It deletes the Item tag record and is not deleting the Item '0' record in the Tag file.

On Debugging this in PY, I found that the table trigger code is not getting executed.

I have checked the code and the Delete on the Tag is present in the F4101 Trigger. For some reason its not reaching that place?

My manager mentioned that a solution was put in place in PY but he doesnt remember what it was.

regards
striker

 

[peterbruce]

Re: Table trigger\'s

Striker,

I take it that this is a JDE table trigger (commonly referred to as Table Event Rules - TER) and not a database table trigger. Please confirm this.

I assume that the "double delete" is happening in PD (production). Please confirm this too.

It would be helpful if you could provide a copy of the ER for both PY and PD. By looking at the differences between the two, it should be evident why the problem only exists in PD.

It is worth noting that with TERs not all the columns are populated, only those columns that are mapped in the table I/O are populated, which presents problems when using TERs.

 

[DBohner-(db)]

Re: Table trigger\'s

Suggestion: Make sure all the Primary Keys are in the Delete Statement (if at all possible).

Print your ER to file - and cut/paste the offending code - so we can have a gander, too.

(db)

 

[striker]

Re: Table trigger\'s

To clarify, this trigger is at the JDE level on F4101.

I'll attach the code in a few hours when I get to work, but I don't see changes in the table trigger with respect to the tag file. All 3 environments (DV, PY & PD) have a TagFile.delete statement in them. I am wondering if there is a way to not execute triggers based on some ini setting or some other way outside of the code.

>> I assume that the "double delete" is happening in PD (production). Please confirm this too.
I debugged in DV, and it does confirm that the delete is being triggered twice (once from Item master & then from the F4101 Table Trigger)

 

[striker]

Re: Table trigger\'s

Here is the code for the NER for F4101 Trigger

 

[David Macek]

Re: Table trigger\'s

First of all I am surprised you have a record with ITM=0. I would try to avoid it because (if I remember well), this causes issues not only in this application. Then you may use the jdedebug.log to see where exactly the delete occurred and which function/trigger did it. Afterwards, you may check the KG for a fix or correct it yourself, but coming back to my first point, you may experience similar issues somewhere else as well.

 

[striker]

Re: Table trigger\'s

I got debug logs for both DV & PY. I notice that when the F4101 JDE trigger is called from DV the item number is being passed as 0 (zero), where as in PY it is being passed properly.

I have attached this part of the log from both DV & PY.

DV:
Jul 12 18:11:13 ** 57940/59864 DELETE FROM PRODDTA.F554101T WHERE ( IMITM = 569408.000000 )
Jul 12 18:11:21 ** 57940/59864 DELETE FROM PRODDTA.F554101T WHERE ( IMITM = 0.000000 )

PY:
Jul 12 17:06:45 ** 61396/56852 DELETE FROM PRODDTA.F554101T WHERE ( IMITM = 569686.000000 )
Jul 12 17:06:52 ** 61396/56852 DELETE FROM PRODDTA.F554101T WHERE ( IMITM = 569686.000000 )

I understand that these double delete's are a bug to begin with. I am trying to convince my client that double deletes are not required.

 

[BOster]

Re: Table trigger\'s

This isn't a resolution to your problem, only an answer to you original question simply for informational purposes.

The only way I know to disable JDE table triggers is on an individual table handle using the JDEBASE API.
Example:


JDB_SetRequestTriggerOption(hReq, JDB_OPTION_ALLTRIGGERS, JDB_ALL_TRIGGERS, JDB_OPTION_OFF)


This is only on the open handle and does NOT globally disable the trigger.

 

[striker]

Re: Table trigger\'s

Jdedebug logs from DV as well as from PY.

Regarding the '0' Item, the client wants to keep using it.

 

[peterbruce]

Re: Table trigger\'s

Striker,

I can see no reason for the difference. I appears that in DV the TK for ITM is not populated, but in PY it is. It could be that using the TK values directly in the F4101 - Plug & Play, F41011.Delete and F554101T.Delete calls results in problems. If you haven't tried this already, try saving the TK ITM value to an evt variable and using the evt variable for the calls. Let us know if that makes any difference.