Create SQL Triggers in SAP HANA – The Process that you can’t ignore!
Moving on in our SAP HANA DataFlair tutorial series, this article focuses on learning the SQL triggers. We will learn about the basics of SQL triggers, their significance in SAP HANA, syntax to create the triggers in SQL script and the process of creating triggers in SAP HANA Studio.
Don't become Obsolete & get a Pink Slip
Follow DataFlair on Google News & Stay ahead of the game
SQL Triggers in SAP HANA
SQL triggers are conceptually similar to stored procedures as triggers are set of SQL statements or stored programs that are automatically executed in response to an event. In SAP HANA, you can make triggers on tables, views, schemas or on databases. Trigger programs get executed or are fired when an INSERT, UPDATE or DELETE operation takes place on a subject table or a subject view.
In SAP HANA, for a given subject table or a view, the user must enable the TRIGGER privilege to be able to create triggers for that specific table or view. Generally, triggers are executed in response to three types of SQL statements:
- Database Manipulation statement (DML statement) such as DELETE, INSERT or UPDATE.
- Database definition statement (DDL statement) such as CREATE, ALTER or DROP.
- Database operation statements like LOGON, STARTUP, SHUTDOWN or SERVERERROR.
Explore the different types of Analytic privileges in SAP HANA
Key Uses of SQL Triggers in SAP HANA
We can primarily use SQL triggers in SAP HANA for these purposes:
- For auditing operations.
- For synchronous replication of data tables.
- To store information on accessing the table.
- Security authorization processes
- To prevent invalid transactions.
- Event logging processes
- To enforce referential integrity.
- To automatically generate derived column values.
Syntax to Create SQL Triggers
We create SQL triggers by a CREATE TRIGGER command. The complete syntax is:
CREATE TRIGGER <trigger_name> <trigger_action_time> <trigger_event_list> ON <subject_table_name> [REFERENCING <transition_list>] [<for_each_row>] BEGIN [<trigger_decl_list>] [<proc_handler_list>] <trigger_stmt_list> END
Syntax Elements to Create SQL Triggers
The two important elements of the syntax to create triggers are <trigger_name> and <trigger_action_time>.
- The <trigger_name> is the name of the trigger which the user assigns. You have to create the trigger with the trigger name along with the operational schema name.
- The <trigger_action_time> specifies the time at which the trigger must get executed. There are three trigger action time statements; BEFORE, AFTER and INSTEAD OF.
1.BEFORE commands the system to execute the trigger before execution of DML statement.
2. AFTER commands the system to execute the trigger after the execution of DML statement.
3. INSTEAD OF commands the system to execute the system in place of executing the DML statement.
Don’t forget to check! – SQL Statements in SAP HANA
How to Create SQL Triggers in SAP HANA Studio
In this section, we will learn how to create and use triggers in SAP HANA Studio.
Step 1: Open SAP HANA Studio and make sure that the Administration Console perspective is selected. Login to a SAP HANA database system and expand the Catalog node.
Step 2: Open the schema under which you wish to create the trigger. Upon expanding a schema node of your choice, you will get a list of objects available in that schema. There is also a folder with the name “Triggers” which contains all the triggers made under the schema.
In this explanation, we will create a trigger for a data table which will inform the user every time about the data or record entered in that table and who entered it.
Step 3: Now, to create an SQL trigger, open the SQL Editor by clicking on the system name and then clicking on the SQL Editor icon (present on the bar).
In the SQL Editor, we will create the SQL statements for the trigger. The statement is as follows:
Create trigger DTF.DTFTRIGGER After insert on “DTF”.”DIMEMPLOYEE” for each row Begin INSERT INTO “DTF”.”DTFAUDIT” VALUES ( CURRENT_TIMESTAMP, CURRENT_USER ) ; End ;
In this set of statement, the first statement assigns a name to the current customer, the next statement decides that the trigger will fire after the execution of the operation in DIMEMPLOYEE table.
The piece of code enclosed in Begin and End statements represent the action that the trigger will perform upon execution. In this case, it will take the timestamp and name of the user that has made an entry into the table DIMEMPLOYEE. This information will save in another table, DTFAUDIT.
Step 4: Execute this statement by clicking on the green execute button given on the upper bar. The trigger will create. You can check for it in the Triggers folder, name of the new trigger must be showing.
Step 5: Now, to test the trigger, go to the data table with which our trigger is associated. Right-click on the table name and select Open Content.
The contents of the table will be available.
We’ll add data in a new row in this table and execute the statement. The SQL statement to add a new row is:
Insert into “DTF”. “DIMEMPLOYEE” value (125, 30, 5, ‘DataFlair’, ‘Indore’, ‘MP’)
A new row with Employee ID 125 and related details will successfully add.
Now, as per our trigger, we must get the information of this new record entry in the DTFAUDIT table.
In this table, you will get the timestamp and user name of the user who did the new entry in the DIMEMPLOYEE table. This shows that the trigger worked successfully.
This concludes our tutorial on SAP HANA SQL triggers. We hope our explanation was helpful to you and you understood the concept of SQL triggers and how to create triggers in SAP HANA Administration Console.
Any queries or feedback for us? Do share your thoughts in the comment section.
Check how Kellogg’s enhanced its performance with the help of SAP HANA in Latest SAP HANA Case Study