How to use a column list in a database trigger on SAP HANA

Question

I'm trying to set up a database trigger to log some change information. What I would like to achieve is that whenever a value of my table is updated, the username and date are also saved. I would like to catch this in a trigger.

What I got so far is this:

CREATE TRIGGER "MySchema"."updateTrigger"
AFTER UPDATE EXCEPT OF "ChangedBy", "ValidForm" ON "MySchema"."MySchema.database::model.MyTable"
REFERENCING NEW ROW mynewrow, OLD ROW myoldrow
FOR EACH ROW

BEGIN
    /* UPDATE status values in Request Table */
    UPDATE "MySchema"."MySchema.database::model.MyTable" 
        SET "ChangedBy" = CURRENT_USER, "ValidFrom" = CURRENT_TIMESTAMP
        WHERE "TableId"=:myoldrow.TableId;

I've used this SAP help page from CREATE TRIGGER. But the code posted above doesn't work. It gives back the following:

SQL syntax error: incorrect syntax near "EXCEPT"

So I'm using an AFTER UPDATE trigger, but I need to except on the fields I'm updating within the trigger to not get into an endless loop. All help is appreciated.

Answer 1

The syntax error is most likely due to the fact that the EXCEPT clause only got into the command with HANA SPS12. Are you running SPS12 or newer?

Anyhow, the approach you're taking is not great on many levels and the fact that old ABAP data modelling is still around, doesn't really make it better.

1. The "LAST_CHANGER" and "LAST_CHANGED_DATE" really are not part of the specific entity that is modelled with the table. It's meta information that should be kept elsewhere.

2. Triggers are very platform specific, so with this you set on HANA as the only platform this code will run on. But when you are on a single platform already, you might as well use the proper platform service for this sort of change tracking. In HANA it's called AUDIT and provides a much more flexible and secure approach to this hand-crafted trigger approach.

3. Triggers were, are and will be an administration and troubleshooting nightmare. Without knowing to explicitly look for them, they will be missed in any analysis most of the time. As they do stuff 'magically' in the background, the chance for side-effects is rather high.

4. Triggers also have quite an impact on the update/insert performance as they don't run asynchronously.

5. With triggers you have additional code to create, maintain and test. Any idea how to do that? The mentioned AUDIT functionality requires now additional coding, no maintenance and no testing. Also, the dependency to HANA system data like user_name automatically gets managed for the AUDIT functionality. Your trigger code won't. Think about changing the length of possible user names (happened in earlier HANA releases).

Knowing that all these arguments will likely not be heeded, because triggers are so appealing to developers, below you'll find a working example of how such a thing can be done (on SPS12 - you can just leave out the except clause on earlier releases):

drop table mytab;
create column table mytab (id int, aaa nvarchar(20), bbb nvarchar(20), validfrom date, validto date,
                           last_changed timestamp, last_changer nvarchar(256));


create trigger update_trig 
before update except of validfrom, validto
on mytab
referencing new row newr 
for each row
begin
    newr.last_changed = current_utctimestamp;
    newr.last_changer = current_user;
end;

drop trigger insert_trig;
create trigger insert_trig 
before insert  
on mytab
referencing new row newr 
for each row
begin
    newr.last_changed = current_utctimestamp;
    newr.last_changer = current_user;
end;
truncate table mytab;

insert into mytab (id, aaa, bbb) values (1, 'bla', 'blupp');

select * from mytab;

/*
ID  AAA BBB     VALIDFROM   VALIDTO LAST_CHANGED            LAST_CHANGER
1   bla blupp   ?           ?       2016-12-19 23:10:46.866 DEVDUDE     
*/

update mytab set aaa='lala' where id = 1;

/*
ID  AAA     BBB     VALIDFROM   VALIDTO LAST_CHANGED            LAST_CHANGER
1   lala    blupp   ?           ?       2016-12-19 23:11:01.002 DEVDUDE     
*/

Again, just because this compiles and works in the A-line use case, doesn't mean it's a good idea. It's not.