Cursor or While on Transaction Notification

[WagnerBezerra]

Hi Everyone...

About use cursor or while loops inside transaction notification... what the experts recommend???

Regards,

Wagner +55 81 998866977 (Whatsapp)

[OlehFabri]

Hi Wagner,

While cursor gives you great advantage of manipulating data row-by-row, I would avoid using it within the SBO_SP_TransactionNotification due to potential performance issues and locks.

Cursors can significantly slow down operations because they process data one row at a time. In a TransactionNotification scenario, where the stored procedure is triggered for every add, update, or delete (and some other) action in the database, the use of cursors can lead to delays, impacting user experience and overall system performance.

Cursors often hold locks on data rows they are processing, which can lead to blocking issues. In a busy system like SAP Business One, where multiple transactions are happening simultaneously, these locks can prevent other operations from proceeding, leading to deadlocks or long waiting times for resources to be freed.

While loop still can be performance-demanding; however, you can adjust it to optimize the load.

Best regards,

[Johan_Hakkesteegt]

Hi,

You can use WHILE or CURSOR in the SBO_SP_TransactionNotification stored procedure. However, you can NOT use INSERT, UPDATE, DELETE with system tables.

Regards,

Johan

[ANKIT_CHAUHAN]

Hi  WagnerBezerra,

The use of advanced database objects such as stored procedures and triggers in the SAP Business One databases are not allowed.
Except the SAP provided stored procedure SBO_SP_TransactionNotification modifying and/or creating as well as executing any SAP or non-SAP stored procedures are not allowed as they

• may cause conflicts with SAP Business One processes
• are not portable between different database systems
• cause possible problems during upgrading and/or patching the SAP Business One databases
  ▪ Using DI API or DI Server instead guarantees backward compatibility for
  an Add-On running on a customer system.
  
  
• Therefore the usage of stored procedures in the SAP Business One databases will not
  be accepted regardless of their purpose as to write and/or extract data.
  Similarly as they may get erased during upgrades and conflict with SAP Business One
  processes, triggers are also not allowed in SAP Business One databases.

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[WagnerBezerra]

HI Ankit...

My point is, I need make a comparision into a UDT when a new record or update record, therefor I think using TN for that... Indeed I have some Knowledge about SAP Native procedures is not allowed did anytihing we though... But except use DI API, we have only TN for treatement tests to avoid duplicate datas or something else when its necessary...
Scenario:

We have a couple Tables, Header and Lines... in header assigned with a equipment, and lines we assigned with implements for each equipment, they could be any implements for each, but we need test if on new implement in equipment, the implement dont be duplicate on this equipment, and does not in another equipment too...

If you have any idea, share with us, it will be wellcome

My best,

Wagner