Delete Job in HANA tables while trigger remains active

[regysm21]

Hi Expert,

i am currently implementing some triggers in HANA DB that catch the changes that happen in an a SAP ERP (SAPABAP1-Schema) and write them in a temp-table. For this I have several questions:

1. What I am wondering is that if i schedule a delete job and runs it on this temp-table where the captured rows from trigger are written, and meanwhile a transaction is being processed in the SAP ERP-Tables. Will the trigger capture the change rows, or during the deletion process, the changed rows are lost? Is there something like a lock concept for this case?

2. Is it possible to define some sort of delta buffer for such process or the synchronisation process of the triggers consider also such points.

Thanks

Regys

[andreas_baldauf]

Hi Regys,

1. The record which is inserted by the trigger is not visible to other processes until the COMMIT statement is executed.
So, if the DELETE statement is started before the COMMIT statement, the newly inserted record will not be deleted.
If the DELETE statement is started after the COMMIT statement, the record will be deleted.

2. To prevent the deletion of newly inserted records you should add a WHERE clause to the DELETE statement so that only records which are older than a certain time are deleted
(e.g. DELETE FROM name_of_table WHERE name_of_column_with_timestamp < '2019-02-01 00:00:00').

Best regards,
Andreas

[regysm21]

Hi Andreas,

thanks for the prompt reply as always... 🙂 What exactly do you mean by:"is not visible to other processes until the COMMIT statement is executed."? Do you mean the commit in the original SAP ERP -tables that I am setting the trigger on? What I mean here is that I am deleting in my "temp-table" where the inserted rows are captured from the trigger at the original erp tables.

Regarding the second point, I implemented a procedure that deletes the data on the trigger table not earlier than yesterday so that there is some sort of buffer and seems to solve the issue.

I was thinking that the Sync-Process(while catching the changes in the erp tables with triggers and writting them to "temperory-tables") was already considering the DB-locks for instance or something similiar. I found some SQL-Statements for Transaction Locks but im not sure if I can use them in triggers. Do you think that SET TRANSACTION Statements would be working in the trigger? I mean if they would solve the issue of locks for instance, or they can only implemented with procedures?

Best regards,
Regys