How to avoid losing data during HANA Trigger Maintenance

[frank_peng]

Customer widely uses HANA trigger for the purpose of tracking all the change records. Since we do not have the syntax like ‘alter trigger’ or ‘create or replace trigger’ on HANA, then how to insure there is no data lost during dropping and recreating the trigger.

[lbreddemann · Accepted solution]

The only way to ensure that no data changes happen while triggers are replaced is to prevent all data changes.

One way to do this could be locking the target table before dropping/re-creating the trigger. Personally, I think that this is the least invasive option and the one that allows for minimal disruption.

Other options could be

• to log out and lock every user account that has change access privilege to the table
• to rename the table before the change and rename it back once the new trigger is in place
• creating the new trigger while the old one is still in place, and only remove the old one once the new trigger has been created. This could lead to duplicate logging entries, but that might not be an issue.

Not sure though, why the customer goes through the pain of logging all data changes when the same could be achieved via the auditing feature.

[frank_peng · Accepted solution]

Hi Lars,

Thanks a lot for your help.

I just tried the locking target table option and it does work.

The triggers have more flexibility and can implement complicated logic, that is could be the reason they use it instead of audit feature.

Best Regards,

Frank