Using HANA Database Triggers to capture Selected Column updates

Background:

We had a specific requirement to capture specific column updates for a sensitive transaction table, although this blog doesn’t go into the specifics of the requirement, I hope going through the process of adding the database trigger and recording the changes in the requested structure will highlight some useful database functions (Database Triggers, Transition Variables, Sequences, Identity columns) that are available in SAP HANA.

Disclaimer:

Database Triggers should be approached with caution, large and complex database triggers not a good design approach, are also very hard to maintain and support. This use case was to ensure we captured updates to sensitive data, regardless of the origin of those changes (app tier, db, services etc) and the trigger code was kept lean, just capturing old/new values, the column(s) that have been updated, User & DateTime of update.

Our Sandbox:

CREATE TRIGGER – SAP HANA SQL and System Views Reference – SAP Library

Important notes in relation to SAP HANA db triggers: (As of SP9)

• Events available on Insert/Update/Delete
• Can fire Before ( validate data, prevent and fix erroneous data etc) or After (record and possibly take action based on content)
• Update & Delete event have access to both old/new transition variables.
• Statement level triggers currently only supported against ROW store tables.
• You can’t reference the original table, i.e. the table the trigger is defined on, in the trigger body. According to Rich in the following link, this limitation may be lifted in SPS10, search page for Trigger New SQLScript Features in SAP HANA 1.0 SPS9
• You can define up to 1024 triggers per single table and per DML. This means a table can have a maximum of 1024 insert triggers, 1024 update triggers and 1024 delete triggers at the same time.
• Limited SQL Script syntax supported, the following is not currently supported)
  • DDL or Set session variables
    
  • resultset assignment (select resultset assignment to tabletype),
  • exit/continue command (execution flow control),
  • cursor open/fetch/close (get each record data of search result by cursor and access record in loop),
  • dynamic sql execution (build SQL statements dynamically at runtime of SQLScript),
  • return (end SQL statement execution)

Working Example:

Please excuse the simple nature of the fictitious tables created for this example, these are merely for illustrative proposes 😀

Transaction Table

PRIMARY KEY ( COUNTRY)

Requirements

1. Fire only on Update

2. Capture only changes to the DOLLAR_VALUE & RATING fields.

3. Identify multiple updates on the same row using the same ID field

4. Record updates using an Insert into an audit table (country_acc_audit)

Create section

Trigger Body

All the DDL sql are available in the attached scripts, just highlighting some lines of interest here.

• Using the connection_id plus a sequence value to uniquely identify this update transaction.
• The application_user_name may be relevant for folks who are connecting from an application layer through a common user (e.g. in the case of SAP applications, SAP<SID>), the current user will have the connection user (e.g. SAPSR3).

Test Scenarios

Update 1:

-- 1 row 2 field update
update country_acc_details
set dollar_value = '11000', rating = 11
where country = 'USA';
commit;

Audit table

Note: ID field is an identity column on the Audit table, it’s a built in sequence on the country_acc_audit table. Also note trx_session_id is the same for both records.

CREATE COLUMN TABLE "COUNTRY_ACC_AUDIT" ("ID" BIGINT CS_FIXED GENERATED BY DEFAULT AS IDENTITY NOT NULL ,

————————————————————-

Update 2:

-- 2 rows 1 column, same connection
update country_acc_details
set dollar_value = '1000'
where country IN ('IE', 'IND');
commit;

Audit table

————————————————————-

Update 3:

-- new connection, 10 rows 2 field update
update country_acc_details
set dollar_value = '100000', rating = 1;
commit;

Audit table

Conclusion

Quite a few limitations in what you can use within the Trigger body, I would have liked to use session variables to tie all the updates executed in the same connection. I also had some indexserver crashes on trigger create and trigger execution for queries that joined m_connections & m_service_threads table. Not sure if it was directly related to those tables or the join types, but I need to do more research before opening an incident.

Otherwise the trigger behaves as expected and as you can see above, met the requirements laid out.