DB Update Trigger: SQLScript

[ezenner2]

We're attempting to create a table trigger which hits on update and records all updated fields in a change log table.  We've successfully created a basic trigger which compares hard coded variable names from the new and old row inputs.  For a more dynamic solution, we'd like to loop through all columns in the updated table to record any changes. Once successful, this solution will be applied across many tables (with a large amount of columns) which necessitates the dynamic approach.

The trigger code can be found below and in the attached file. A few notes on it's current state:

- The trigger should watch for updates to table "Projects"

- All field updates should create a row in table "ChangeLog"

- SAP HANA table "TABLE_COLUMNS" is used to read the current table's column structure

- Receiving the error (marked in code below, Line 33):

Could not execute 'CREATE TRIGGER TEST_PROJECT_NAME_CHG AFTER UPDATE ON "Projects" ...'

SAP DBTech JDBC: [1287]: identifier must be declared: NEW_ROW

We've looked all over SCN and read through the SQLScript Reference guide, but cannot find a solution.  This feels close, so I'm hoping a SQLScript expert could help take this to the finish line.

* Note, this is for custom tables which are edited via a large XS application. Therefore, we've determined that using a trigger (if possible) is the best approach.

Code

CREATE TRIGGER TEST_PROJECT_CHG

AFTER UPDATE ON "Projects"

REFERENCING NEW ROW new_row, OLD ROW old_row   

FOR EACH ROW                                            

BEGIN

   /* Data declaraions */

   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; 

   DECLARE table_name NVARCHAR(200) := 'Projects';

   DECLARE tbl_columns NVARCHAR(256) ARRAY;

   DECLARE tbl_indexes NVARCHAR(16) ARRAY;

   DECLARE l_index INTEGER; 

   DECLARE l_count INTEGER;

   DECLARE lt_tbldef TABLE (COLUMN_NAME NVARCHAR(256), INDEX_TYPE VARCHAR(16));

   DECLARE v_column_name NVARCHAR(256);

   DECLARE new_val NVARCHAR(1000);

   DECLARE old_val NVARCHAR(1000);

   /* Get session information */

   SELECT top 1 current_connection,

     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;

/* Determine the number of table columns to loop through  */

SELECT COUNT(*) INTO l_count FROM "TABLE_COLUMNS" WHERE "TABLE_NAME" = table_name;

FOR l_index IN 1 .. l_count DO

  SELECT "COLUMN_NAME" INTO v_column_name FROM "TABLE_COLUMNS" WHERE "TABLE_NAME" = table_name AND "POSITION" = l_index;

  /* ERROR! Attempting to access the column data based on the v_column_name variable.  */

  SELECT v_column_name INTO new_val FROM :new_row;

  SELECT v_column_name INTO old_val FROM :old_row;

  /* Update Change Log table where the new column value is different than the old value */

  IF :old_val <> :new_val THEN

  v_group_seq_id := :v_group_seq_id + 10;

  insert into "ChangeLog" (

  "trxId",

  "trxCntInd",

  "table",

  "mainKey",

  "fullKey",

  "operation",

  "changeDt",

  "userName",

  "fieldName",

  "newValue",

  "oldValue"

  ) VALUES (

  :v_group_id,

  :v_group_seq_id,

  :table_name,

  :new_row."id",

  :new_row."id",

  'U',

  :v_curr_timestamp,

  :v_app_user_name,

  :v_column_name,

  new_row[:v_column_name],

  old_row[:v_column_name]);

  END IF;

END FOR;

END;

Update Trigger - SQLScript reference error.txt.zip

[lbreddemann · Accepted solution]

Nope, not sensibly doable within triggers.

You'd be better off writing yourself a procedure that generates non-dynamic trigger code for a given table.

Besides, why don't you use the AUDIT LOG instead? It pretty much does that already...

[ezenner2 · Accepted solution]

Lars,

You're spot on with this advice. I was making things more complicated than they needed to be. My early research didn't reveal AUDIT_LOG, but this solution fits our needs. Appreciate the quick help!  There still appears to be a gap in functionality when wanting to develop advanced triggers, however that is for another discussion.

For future reference, I compiled a few helpful AUDIT_LOG links:

Turning on Auditing:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d3d56075191014af43d6487fcaa603/content.htm

The Audit Log Table:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/d1/fe1244d29510148f69be8b0e060dcc/content.htm

Clearing the Audit Log:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/d2/231bd2d2951014ad35d218ea6628c8/content.htm

http://saphanawiki.com/how-to-truncate-audit_log-in-hana/

Thanks,

Eric Zenner

[ezenner2 · Accepted solution]

Lars,

The message before may have been premature. I successfully created an Audit Policy for the Projects table using these SQL statements:

1) CREATE AUDIT POLICY PROJECT_CHANGE AUDITING SUCCESSFUL UPDATE ON "Projects" LEVEL INFO;

2) ALTER AUDIT POLICY PROJECT_CHANGE ENABLE;

Unfortunately, an entry was made to the AUDIT_LOG table without any transaction level detail. The audit log does not contain the new or old field values which we would like to capture on update.  Attached is a screenshot of this update entry.

Is there additional set up that needs to be done when creating the Audit Policy or does the change recording not set up to record table change values?  If not, is there a different recommended approach for our needs?

Thanks,

Eric

[lbreddemann · Accepted solution]

Right, the old/new columns in the audit table are for db parameter changes not for table data. That one slipped my memory...

If you really want to cover a data change log on table level, I still would go with the approach of generating trigger code that is not dynamic.

The less logic you put into the trigger, the better and the less the impact to your insert/update performance.