The client, in this situation, wanted to Check-in an item to a location other than the default Assigned Location value. We can use Transaction Pre-Authorization to force a change in the Assigned Location field during Check-in, however this happens after the transaction has updated the RELATION table. Thus reports on the location of the item would still show in the old Assigned Location.
This particular instruction shows how to use ‘Trigger’ in SQL to make BACKTRACK updates the field in RELATION table to match the Assign_Loc in Item table. We have to do this because the application must make allowance for items to be checked-in to a different location than where they originated. We use extra field to update the Assign_Loc but the p_appl_no field in RELATION table does not get properly updated. When running the report, BACKTRACK will pull data from RELATION table instead of ITEM table (even when explicitly stated that the field is from ITEM table).
In order to make this work, we have to utilize the trigger mechanism in SQL. We have to be cognizant of the fact that the transaction may involve multiple record updates in the item table at one time.
- Get the largest TRAN_SEQ in the TRANS table – this indicates the last row created
- Get the ITEM_No from that TRANS record
- Using the ITEM_No from the TRANS record, get Assign_Loc from the ITEM table
- Update the P_APPL_No = Assign_Loc (From ITEM) for the record in RELATION table that has the C_ITEM_NO = ITEM_NO
There may be a more efficient way to get SQL to do this. However, this code is much simpler than using Join statements in SQL.