Does hana DB tables support triggers on insert on update to create a row in log table

[Former Member]

In sql server i use triggers on table, when a new record is inserted or an existing repor is update, create a copy of that row to another log table.

This is useful in terms of managing delta's on any tables data.

This is how i create:

CREATE TRIGGER [dbo].[insMARA_Changelog] ON [dbo].[MARA]
FOR INSERT, UPDATE
AS

DECLARE @TimeStamp datetime

Set @TimeStamp = GetDate()

INSERT INTO MaraChange_log
(all fields here)
SELECT
fields here from mara table
FROM inserted ins JOIN mara na ON
na.maraprimarykeyID = ins.maraprimarykeyID

Can someone please provide details how to use table based triggers on insert on update on a table record to create a copy of that row to a change log table.

Thanks a lot for the helpful info.

[former_member184768]

Hi,

I am sure the other fellow members on this community have provided you enough reference links for your requirement, but I need to mention a different point here.

Please check the transaction behavior in the trigger, which means the following:

1) Can you commit the row inserted in the LOG table from the trigger. Is it allowed. This could be a situation where the inner transaction is expected to behave independent of the outer transaction.

2) What happens if the insert / update transaction which fires the trigger is rolled back due to some error / constraint. Should it also rollback the LOG table entry or keep it committed.

Regards,

Ravi

[Former Member]

Hi Guy,

You can get more info from this link: http://help.sap.com/hana/html/sql_create_trigger.html

[rama_shankar3]

Guy:

Yes, HANA support database triggers.

The syntax is:

CREATE TRIGGER <trigger_name> <trigger_action_time> <trigger_event>

ON <subject_table_name> [REFERENCING <transition_list>]

[<for_each_row>]

  BEGIN

  [<trigger_decl_list>]

  [<proc_handler_list>]

  <trigger_stmt_list>

  END

Regards,

Rama

[Former Member]

Thanks a lot RAMA, please do you have a sample table based triiger on insert on update.

I know how to do it via BODS using table comparison, history preservation, key gen etc.

BUt when we use Trigger based SLT, just to have this based on a table to create a copy of each change whether insert or update.

Regards,

Guy

[rama_shankar3]

Guy:

Please go through the samples referred in the below link:

http://help.sap.com/hana/html/sql_create_trigger.html

I have personally not used triggers in conjunction to SLT. Take extra caution with SLT and triggers.

Hope this helps.

Regards,

Rama

[Former Member]

When you use SLT, trigger is on ECC not on HANA. What do you want to do exactly? What is your case?