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
- 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.
- 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:
- INSERT:
Activates when a new row is inserted into the table.
- UPDATE:
Activates when an existing row is updated.
- 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:
- BEFORE:
Executes before the triggering event. Used for validation or modification
of data before it is committed.
- AFTER:
Executes after the triggering event. Used for logging, enforcing business
rules, or cascading changes to other tables.
- 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 !