What are triggers in SAP HANA environments ?

 Triggers in SAP HANA environments are database objects that automatically execute a specified set of SQL statements or logic in response to certain events on a particular table or view. They are used to enforce business rules, maintain data integrity, and automate system processes. Here’s a detailed overview of triggers in SAP HANA:

Example:

CREATE TRIGGER
  "_SYS_STATISTICS"."STATISTICS_SWITCH_PROFILE"
AFTER UPDATE ON
  "_SYS_STATISTICS"."STATISTICS_PROPERTIES"
REFERENCING OLD ROW MYOLDROW, NEW ROW MYNEWROW FOR EACH ROW
begin  
  if :mynewrow.key = 'internal.sizing.profile' then
    if :mynewrow.value != :myoldrow.value then
      call _SYS_STATISTICS.shared_copy_profiles (:myoldrow.value, :mynewrow.value);
    end if;
  end if;
end

 

Types of Triggers in SAP HANA

1. Row-Level Triggers: These triggers execute once for each row affected by the triggering event. They are useful for enforcing row-specific constraints or logging changes.
2. Statement-Level Triggers: These triggers execute once for the entire SQL statement, regardless of how many rows are affected. They are used for actions that apply to the whole statement, such as auditing.

Trigger Events

Triggers can be defined to execute in response to the following events:

1. INSERT: Activates when a new row is inserted into the table.
2. UPDATE: Activates when an existing row is updated.
3. DELETE: Activates when a row is deleted from the table.

Timing of Triggers

Triggers can be specified to execute at different times relative to the triggering event:

1. BEFORE: Executes before the triggering event. Used for validation or modification of data before it is committed.
2. AFTER: Executes after the triggering event. Used for logging, enforcing business rules, or cascading changes to other tables.
3. INSTEAD OF: Executes in place of the triggering event. Commonly used for views to provide custom behavior for insert, update, or delete operations.

This trigger is executed in context of the SAP HANA statistics server (SAP Note 2147247) after updates on table STATISTICS_PROPERTIES in order to make sure that under certain key / value conditions the procedure SHARED_COPY_PROFILES is called in order to perform follow-up actions.

2800020 - FAQ: SAP HANA Triggers

Thanks !