Using HANA Database Triggers to capture Selected Column updates

[former_member184795]

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 :grin:

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.

[Former Member]

I am getting the below error when trying update the field after creating the trigger(using same code as provided in post): any suggestions?

Could not execute 'update country_acc_details set dollar_value = '11000', rating = 11 where country = 'USA'' in 6 ms 568 µs .

SAP DBTech JDBC: [1299]: no data found: no data found

[former_member184795]

hi Akash,

     If the values didn't exist in the table country_acc_details, the update still should still execute and just report 0 rows updated, so you a different issue.

I would recommend start with the DDL script again and drop all the objects and re-create them in the script. You may be missing 1 or more objects.

If you are satisfied that you have all the objects created and inserted the records per the script, i would disable/drop the trigger and try the update again. If the update works after the trigger is dropped, the issue is in the trigger code.

Sean.

[Former Member]

hi sean,

thanks for the reply.

when i drop the trigger then the update works fine.

i will list down the steps performed using the code provided by you,please let me know if i missed anything.

step1- create both the tables (COUNTRY_ACC_AUDIT and COUNTRY_ACC_DETAILS)

step2: create intries in table COUNTRY_ACC_DETAILS.

step3: create sequence COUNTRY_ACC_DETAILS_S

step4: create trigger  COUNTRY_ACC_DETAILS_S.

step 5: update the entries in table.

i am sure that the code being used by me is same as provided by you.

also let me know if i need to drop any object during the steps.

i have created a documents with the steps that i am following along with the code and screenshots.please have a look at it.

trigger.docx - Google Drive

[former_member184795]

hi Akash, if you dropped the trigger and the update executed correctly, then the issue is with the trigger. I would suggest you take a look at the trigger and see if you can execute each statement in it. e.g. SELECT top 1 current_connection||'-'||country_acc_details_S.nextval, MAP(mt.application_user_name,'',user_name,mt.application_user_name), current_timestamp FROM m_service_threads as mt WHERE mt.connection_id = current_connection What version of HANA are you running your tests on?

[Former Member]

hi Sean,

i removed the parts of the trigger code related to SELECT top 1 current_connection||'-'||country_acc_details_S.nextval, MAP(mt.application_user_name,'',user_name,mt.application_user_name), current_timestamp FROM m_service_threads as mt WHERE mt.connection_id = current_connection .

after doing this change it is working. i am able to capture the old and new values. but i also need the timestamp and username. can you please help that why that part is not working.

i am using

SAP HANA studio 

Version: 1.00.82.0 

Internal Version: 1.82.6 (1826)

[former_member184795]

hi Akash,

     If you don't care for the application user name, you can just use session variables.

E.g. select current_user, current_timestamp from dummy

What do you get when you execute the Select from m_service_threads in a sql editor session?

[Former Member]

hi sean,

i will try that and will reply..mean while i wanted to know that can stored procedure be used for acheiving exactly the same requirement?

if yes then can you explain it.

also if yes then then which method will be best trigger or procedure.?

[Former Member]

hi Sean,

wanted to know that while referencing NEW ROW can we use a variable to pass the field name.As i am getting syntax error at " new_row.:v_fieldname".for below code example.

ex-

v_fieldname : = ' KUNNR';

IF :new_row.:v_fieldname <> :old_row.v_fieldname

THEN insert

into "RGAJULA"."ZOTC_SETTLE_T2"( ZSETTLEDOC,

  KUNAG,

  OLD_VALUE,

  NEW_VALUE ) VALUES ( :new_row.ZSETTLEDOC,

  :new_row.KUNAG,

  :old_row.:v_fieldname,

  :new_row.:v_fieldname )

;

END

IF

;

[former_member184795]

hi Akash,

     The :old_row & :new_row specifically refer to the row of the table being modified, the database trigger fired on update of a row. So you use the :new_row & :old_row  syntax to reference those column values from the current table row being modified.

You can insert your variable value into the table as above by removing :new_row & :old_row, but I don't understand why/what the :new_row & :new_row should represent? In any case, it gives a syntax error as there is no column in the table row with this name.

Sean.

[Former Member]

the thing is that i want to capture the field name dynamically. so that my same insert code can be written in loop to apply for multiple field changes. so i want new and old values of a field whose name will be stored in a variable.

[former_member184795]

hi Akash, in that case you would need to use dynamic sql to build up the string for insert, but as noted above in the original article, this is not currently supported in database triggers.

[Former Member]

Also as we capture the application user name , in the same way can we capture application T-code( FromECC)

[Former Member]

Hi,

We stuck with a scenario in triggers,

Example: there are three tables A&B&C.

I wanted to write a trigger on table A. when ever a record is inserted or updated in to table A, this trigger should work and get the records from table B. If there are four records which are relevant to the new record in table B, all the four records should be edited by taking the values of new record from A and to be inserted in the table C.

Is there any possibility to store the multiple records by writing any select statements and store it in the temporary table.?

kindly help me by providing suggestions.

Thanks & regards

Sadanand B R

[asj_bcn]

Hi,

I am also experiencing problems when using triggers. I think you may be able to help me.

In my case I have a table in BW. I want to access to this table, pre-process it and apply a AFL predictive function (Single Exponential Smoothing). Then, I want to store the results and access them from BW. After some research, I managed to find 5 different ways to approach this problem:

1. Using SQLScript from SAP HANA Studio


2. Using Application Function Modeller (AFM) from SAP HANA Studio


3. Using SAP Predictive Analytics (SAP PAA)


4. Use BW report (ABAP) to trigger a SQL PROCEDURE


5. Using Predictive Algorithms native in BW

My question remains at most time the same: How should I modify my trigger to be able to launch my AFM/SQL/SAP PAA procedure?

Please, have a look to https://answers.sap.com/questions/148420/five-approaches-to-execute-a-predictive-afl-functi.html.

Thanks and regards!

[Former Member]

Excuse me, I'm new here. Where can I finde the attached scripts?

Thanks!

[former_member184795]

--set schema "XXXX"



--drop table country_acc_details;



create column table country_acc_details (

country nvarchar(3) not null,

account_number nvarchar(10) not null,

dollar_value decimal,

rating int,

created_at timestamp,

created_by nvarchar(10),

changed_at timestamp,

changed_by nvarchar(10),

PRIMARY KEY (country)

);



insert into country_acc_details  values (

'USA', 'ACC-1', 1000, 1, current_timestamp, 'USER-A', current_timestamp, 'USER-A'

);

insert into country_acc_details  values (

'UAE', 'ACC-2', 2000, 2, current_timestamp, 'USER-B', current_timestamp, 'USER-B'

);

insert into country_acc_details  values (

'IND', 'ACC-3', 3000, 3, current_timestamp, 'USER-C', current_timestamp, 'USER-C'

);

insert into country_acc_details  values (

'MEX', 'ACC-4', 4000, 4, current_timestamp, 'USER-D', current_timestamp, 'USER-D'

);

insert into country_acc_details  values (

'SCO', 'ACC-5', 5000, 5, current_timestamp, 'USER-E', current_timestamp, 'USER-E'

);

insert into country_acc_details  values (

'ENG', 'ACC-6', 6000, 6, current_timestamp, 'USER-F', current_timestamp, 'USER-F'

);

insert into country_acc_details  values (

'IE', 'ACC-7', 7000, 7, current_timestamp, 'USER-G', current_timestamp, 'USER-G'

);

insert into country_acc_details  values (

'FRA', 'ACC-8', 8000, 8, current_timestamp, 'USER-H', current_timestamp, 'USER-H'

);

insert into country_acc_details  values (

'BRL', 'ACC-9', 9000, 9, current_timestamp, 'USER-I', current_timestamp, 'USER-I'

);

insert into country_acc_details  values (

'SWE', 'ACC-10', 10000, 10, current_timestamp, 'USER-J', current_timestamp, 'USER-J'

);

commit;



create column table country_acc_audit

         (ID bigint not null primary key generated by default as IDENTITY,

          TRX_GROUP_ID nvarchar(50),

          COL_NAME nvarchar(30),

          OLD_VALUE nvarchar(50),

          NEW_VALUE nvarchar(50),

		  created_at timestamp,

		  created_by nvarchar(10)        

          );

          



--drop sequence country_acc_details_S;

create sequence country_acc_details_S start with 100;



--select country_acc_details_S.nextval from dummy;



--select * from COUNTRY_ACC_DETAILS



-- 1 row 2 field update

update country_acc_details

set dollar_value = '11000', rating = 11

where country = 'USA'



--DROP TRIGGER CTRY_ACC_DLTS_U;

CREATE TRIGGER CTRY_ACC_DLTS_U

 AFTER UPDATE ON COUNTRY_ACC_DETAILS

 REFERENCING NEW ROW new_row, OLD ROW old_row   

 FOR EACH ROW

 

 BEGIN

 

  DECLARE v_group_id NVARCHAR(20);

  DECLARE v_group_seq_id INT := 0;

  DECLARE v_app_user_name NVARCHAR(20);

  DECLARE v_curr_timestamp TIMESTAMP;  

 	

 	SELECT top 1 current_connection||'-'||country_acc_details_S.nextval, 

 	    MAP(mt.application_user_name,'',user_name,mt.application_user_name),

	 	current_timestamp 

 		INTO v_group_id, v_app_user_name, v_curr_timestamp 

	FROM m_service_threads as mt 

	WHERE mt.connection_id = current_connection;



IF :new_row.dollar_value <> :old_row.dollar_value THEN 

	v_group_seq_id := :v_group_seq_id + 10;

	

	insert into country_acc_audit(

		TRX_SESSION_ID,

		TRX_COUNT_IND,

		COUNTRY,

		COL_NAME,

    	OLD_VALUE,

		NEW_VALUE,

		CREATED_AT,

		CREATED_BY

	) VALUES ( 

		:v_group_id,

		:v_group_seq_id,

		:new_row.country,		

		'DOLLAR_VALUE',

		:old_row.dollar_value,

		:new_row.dollar_value,

		:v_curr_timestamp,

		:v_app_user_name);

 

END IF;



IF :new_row.rating <> :old_row.rating THEN 

	v_group_seq_id := :v_group_seq_id + 10;

	

	insert into country_acc_audit(

		TRX_SESSION_ID,

		TRX_COUNT_IND,

		COUNTRY,		

		COL_NAME,

    	OLD_VALUE,

		NEW_VALUE,

		CREATED_AT,

		CREATED_BY

	) VALUES ( 

		:v_group_id,

		:v_group_seq_id,

		:new_row.country,

		'RATING',

		:old_row.rating,

		:new_row.rating,

		:v_curr_timestamp,

		:v_app_user_name);

 

END IF;

 	

 END;	

 

Hi Felix, this is old but I hope the scripts above help..

 

Sean.