HANA Audit Table storing "?" in columns

[Former Member]

I'm absolutely new to SAP and am trying to implement the Audit Log on HANA.

I have successfully made it so that every Create, Update, Delete of a record inserts a row into AUDIT_LOG table.

However, many of the values are stored as "?" such as PREV_VALUE, VALUE, KEY, etc.

Under the STATEMENT Column, the update statement also doesn't provide sufficient information.

update "TDH"."TABLEEXAMPLE" PERM set "SNO"=TEMP."SNO","EXAMPLE1"=TEMP."EXAMPLE2","EXAMPLE4"=TEMP."ACCOM_PAID_IND","VALID_FROM"=TEMP."VALID_FROM","VALID_TO"=TEMP."VALID_TO","ACTIVE_FLAG"=TEMP."ACTIVE_FLAG","LAST_UPDATE_DTE_TIME"=TEMP."LAST_UPDATE_DTE_TIME","LAST_UPDATE_BY"=TEMP."LAST_UPDATE_BY" from "TDH"."TABLEEXAMPLE" PERM, "#EXAMPLE3" TEMP where PERM."KEY"=?,(4)

I have edited some information on the statement to ensure our table names are not shown.

May I know what could have went wrong or is this expected?

Please see below image as an example

[lbreddemann · Accepted solution]

The audit log does not track the values of table records that have been changed.

The columns PREV_VALUE and VALUE belong to a group of columns concerned with database parameter changes (FILE_NAME, SECTION, KEY, PREV_VALUE, VALUE).

As every entry in the AUDIT_LOG view represents a single audited action, it's clear that e.g. mass updates cannot be logged together with the before and after values of all affected columns/tables.

Other columns in the AUDIT_LOG view cover privileges, error codes and remote database access.

[Former Member · Accepted solution]

Hi Lars.

Much thanks for the answer. May I check that if I want to track what has been changed on a certain table, the old and new values, I will then have to create my own custom triggers / other ways to track what was changed?

[lbreddemann · Accepted solution]

There's no additional HANA feature that would automatically do that.

Personally, I am not a fan of this kind of "just in case"-logging via triggers due to the many issues that db-triggers bring with them. If there actually is a business need to be able to review record changes with the before and after values, then that need usually cannot easily be served with just one set of triggers on a single table. So, now you're having to "log" multiple connected tables and you have to have a way of reviewing the application record changes in context, e.g. join dependent tables correctly together to see what business records got changed.

This sort of requirement is IMO better solved by implementing the logging with the data change code. That can be on ABAP level via the table logging or instead of building triggers, implement data changes through procedures that cover both the update of the tables and keeping the record log.

Both of these options lead to manageable and easy to understand systems while introducing db-triggers can lead to confusion and misunderstanding. Having db-triggers on a table is not obvious at all (you don't "see" them in any admin or development tool unless you explicitly look for them). Also, they are highly dependent on the source table structure and if you are not able to automatically re-deploy the db-triggers after a change in the source- or target-tables then you end up with a lot of unwarranted maintenance work.

All in all, a db-trigger is a rather crude low-level construct but application record logging is a high-level requirement. To me, it's not the right tool for the job.

- Lars