Skip to Content
0
May 09, 2016 at 10:00 PM

DB Update Trigger: SQLScript

213 Views

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;