Caveat Emptor for Table Triggers

[BBritain]

Hey Gang,

I recently posted a problem about table trigger TK values not populating - specifically the key TK values. I opened an issue with PeopleSoft, and was able to have a conference call with a Senior Technical Consultant.

Currently table triggers populate the TK values based upon the SELECT.....FOR UPDATE sql statement. If a business function does not include the key fields for update, then they cannot be seen in the table trigger events. In a similar problem, Table IO - Deletes can also result in TK key values not being populated. If the Delete is preceded by a related Select (and I would assume FetchNext), then the TK values are populated.

We are currently asking for an Enhancement SAR to populate the TK values with key fields as well as items from the WHERE clause (which are not currently populating).

But for now, I just want TT developers to be aware that events can be triggered when the keys are unknown, and developers should code accordingly.

Ben again

 

[DBohner-(db)]

Thanks, Ben.

It will be very helpful if the changes do come about. Can you append any SAR numbers (when/if they come available)?

Daniel

 

[Zoltan_Gyimesi]

Hi Ben,

Does the reported issue, that when I use partial key update or delete then the corresponding table triggers won't work correctly because not all TK the fields are populated?

Am i right?

I really hope I am wrong and I misunderstood something only.

Regards,

Zoltán

 

[BBritain]

I will post SAR numbers if/when they become available - if we get enough customers 'signed up' on a SAR, the better chance we have of getting the request approved.

Now to the reported issue. I am not sure of the specific cases in which table triggers fall short. I will be testing specific cases and reporting them here.

But I believe you are right, and have something to worry about. Let me restate the situation that got me to this point.

I put a table trigger on the F41021 to update a custom table. That table trigger is intended to notify me of a potential change in quantity available for specific Item/Locations. When a user clicks the OK button on Sales Order Entry, the quantity committed is updated by P4210 (business function) which changes the quantity available. Well, in this case the table trigger does not know what record has been updated, so I am at a loss because the key fields have not been loaded into the TK values.

In debug, the SQL statement (the SELECT ...FOR UPDATE...) does not include the key fields in the FOR UPDATE area so they are not loaded to the TK values. I was told that a similar problem exists for Table IO Deletes without a Select.

In a related note: Table Triggers currently do not have the ability to discern between NULL and <Blank>. Why do we care, you ask?

Suppose I have Table IO that updates a Catagory Code 05 field. If the value is already set and you want to clear it, just pass a <Blank> into the proper parameter. Now suppose another part of your code you don't even map a parameter to Catagory Code 05. So the table IO updates the field in the first example but not the second.

The problem is when you are in the table trigger, you can't tell the difference. The TK Cat Cde 05 looks the same for both examples. If your code wants to make a decision based upon this field, then you can get into trouble.

Ben again - Have a nice day!

 

[BBritain]

This is a follow-up on the Table Trigger issue.

As a reminder, Sales Order Entry when it updates tables through the MBF (Master Business Functions), does so in such a way that table trigger events cannot tell what records are being updated (As far as I know, this applies to every table updated by Sales Order Entry - SOE).

This means that if you have a table trigger on any table that SOE modifies - It is probably not working. Peoplesoft has said that they will open up a SAR but I have not seen it yet, probably because of the holidays.

In the meantime I have effectively tripped the table trigger by creating a 'do nothing' business function which takes the key fields, reads the 'Time of Day' (TDAY) and rewrites it - causing the trigger to process. This business function is placed in the 'Grid Record Changed & Exited' function. It's not the perfect choice but it is the best way I could think of - to trip the table trigger. The table trigger when tripped by this business function, CAN tell what record is updated and works fine so far.

I will further update this if we get a SAR (fingers crossed).

Ben again

 

[BBritain]

Ok, this is one of those dead horses that I continually beat, I just want to make sure the latest information is available for those searching the archives.

Because of a recent post regarding table triggers, I have tested more. I previously stated that Sales Order Master Business Functions created updates in such a way that the table triggers do not work. Well, I have tested specifically on the F4211 and CAN get the key fields in the table trigger when processing through the Sales Order Entry. My previous problem was getting key values in the triggers for supporting tables such as the F41021, and possibly others. Just wanted to correct myself.

Ben again,

 

[Frosty the Coder]

I should probably start a new thread for this BUT,
a co-worker is trying to update 4211 URDT from w/in the F4211 TERs.

Basically, if the date is null, populate it w/today's date.

He's not been able to get it to work, and help desk has said that it won't.

Reading this, and the other thread, should he be able to:
(if urdt is null) popluate this column from w/in the F4211 TER (on a 400)?
Does he need a unique bsvw as suggested in the other thread?

 

[BBritain]

Well just for fun, I created the trigger:

=======================================================================
TABLE: Sales Order Detail File
=======================================================================
EVENT: Before Record is Updated
-----------------------------------------------------------------------
0001 // test
0002 If TK DocumentOrderInvoiceE is greater than <Zero>
0003 TK UserReservedDate = SL DateToday
0004 Else
0005 End If

Compiled it and then hit Sales Order Detail and then OK. It updated the table alias perfectly. You might have him try the test 'if date is less than ...say 12/07/1942 and then populate it - it may be related to adding <Null Date> into the logic. I am on B7334 sp22.

Ben again,