{"id":56830,"date":"2019-05-25T14:41:21","date_gmt":"2019-05-25T09:11:21","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=56830"},"modified":"2021-12-04T13:00:30","modified_gmt":"2021-12-04T07:30:30","slug":"create-sql-triggers-in-sap-hana","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/","title":{"rendered":"Create SQL Triggers in SAP HANA &#8211; The Process that you can&#8217;t ignore!"},"content":{"rendered":"<p>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.<\/p>\n<h3>SQL Triggers in SAP HANA<\/h3>\n<p>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.<\/p>\n<p>Trigger programs get executed or are fired when an<strong> INSERT, UPDATE or DELETE<\/strong> operation takes place on a subject table or a subject view.<\/p>\n<p>In SAP HANA, for a given subject table or a view, the user must enable the<strong> TRIGGER privilege<\/strong> to be able to create triggers for that specific table or view. Generally, triggers are executed in response to three types of SQL statements:<\/p>\n<ul>\n<li>Database Manipulation statement (DML statement) such as <strong>DELETE, INSERT<\/strong> or <strong>UPDATE<\/strong>.<\/li>\n<li>Database definition statement (DDL statement) such as <strong>CREATE<\/strong>, <strong>ALTER<\/strong> or <strong>DROP<\/strong>.<\/li>\n<li>Database operation statements like <strong>LOGON<\/strong>, <strong>STARTUP<\/strong>, <strong>SHUTDOWN<\/strong> or <strong>SERVERERROR<\/strong>.<\/li>\n<\/ul>\n<h3>Key Uses of SQL Triggers in SAP HANA<\/h3>\n<p>We can primarily use SQL triggers in SAP HANA for these purposes:<\/p>\n<ul>\n<li>For auditing operations.<\/li>\n<li>For synchronous replication of data tables.<\/li>\n<li>To store information on accessing the table.<\/li>\n<li>Security authorization processes<\/li>\n<li>To prevent invalid transactions.<\/li>\n<li>Event logging processes<\/li>\n<li>To enforce referential integrity.<\/li>\n<li>To automatically generate derived column values.<\/li>\n<\/ul>\n<h3>Syntax to Create SQL Triggers<\/h3>\n<p>We create SQL triggers by a<strong> CREATE TRIGGER<\/strong> command. The complete syntax is:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CREATE TRIGGER &lt;trigger_name&gt; &lt;trigger_action_time&gt; &lt;trigger_event_list&gt;\r\nON &lt;subject_table_name&gt; [REFERENCING &lt;transition_list&gt;]\r\n[&lt;for_each_row&gt;]\r\nBEGIN\r\n[&lt;trigger_decl_list&gt;]\r\n[&lt;proc_handler_list&gt;]\r\n&lt;trigger_stmt_list&gt;\r\nEND<\/pre>\n<h3>Syntax Elements to Create SQL Triggers<\/h3>\n<p>The two important elements of the syntax to create triggers are &lt;trigger_name&gt; and &lt;trigger_action_time&gt;.<\/p>\n<ul>\n<li>The<strong> &lt;trigger_name&gt;<\/strong> 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.<\/li>\n<li>The <strong>&lt;trigger_action_time&gt;<\/strong> specifies the time at which the trigger must get executed. There are three trigger action time statements;<em> BEFORE<\/em>, <em>AFTER<\/em> and<em> INSTEAD OF<\/em>.<\/li>\n<\/ul>\n<p>1. <strong>BEFORE<\/strong> commands the system to execute the trigger before execution of DML statement.<\/p>\n<p>2. <strong>AFTER<\/strong> commands the system to execute the trigger after the execution of DML statement.<\/p>\n<p>3. <strong>INSTEAD OF<\/strong> commands the system to execute the system in place of executing the DML statement.<\/p>\n<h3>How to Create SQL Triggers in SAP HANA Studio?<\/h3>\n<p>In this section, we will learn how to create and use triggers in SAP HANA Studio.<\/p>\n<p><strong>Step 1:<\/strong> Open <strong>SAP HANA Studio<\/strong> and make sure that the <strong>Administration Console<\/strong> perspective is selected. Login to a SAP HANA database system and expand the <strong>Catalog<\/strong> node.<\/p>\n<p><strong>Step 2:<\/strong> 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 \u201c<strong>Triggers<\/strong>\u201d which contains all the triggers made under the schema.<\/p>\n<p>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.<\/p>\n<p><strong>Step 3:<\/strong> Now, to create an <em>SQL trigger<\/em>, open the <strong>SQL Editor<\/strong> by clicking on the system name and then clicking on the SQL Editor icon (present on the bar).<\/p>\n<p>In the SQL Editor, we will create the SQL statements for the trigger. The statement is as follows:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">Create trigger DTF.DTFTRIGGER\r\nAfter insert on \u201cDTF\u201d.\u201dDIMEMPLOYEE\u201d for each row\r\nBegin\r\nINSERT INTO \u201cDTF\u201d.\u201dDTFAUDIT\u201d\r\nVALUES\r\n(\r\nCURRENT_TIMESTAMP, CURRENT_USER\r\n)\r\n;\r\nEnd\r\n;<\/pre>\n<p>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 <strong>DIMEMPLOYEE<\/strong> table.<\/p>\n<p>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 <strong>DIMEMPLOYEE<\/strong>. This information will save in another table, <strong>DTFAUDIT<\/strong>.<\/p>\n<p><strong>Step 4:<\/strong> 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 <strong>Triggers<\/strong> folder, name of the new trigger must be showing.<\/p>\n<p><strong>Step 5:<\/strong> Now, to test the trigger, go to the data table with which our trigger is associated. Right-click on the table name and select <strong>Open Content<\/strong>.<\/p>\n<p>The contents of the table will be available.<\/p>\n<p>We\u2019ll add data in a new row in this table and execute the statement. The SQL statement to add a new row is:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">Insert into \u201cDTF\u201d. \u201cDIMEMPLOYEE\u201d value (125, 30, 5, \u2018DataFlair\u2019, \u2018Indore\u2019, \u2018MP\u2019)<\/pre>\n<p>A new row with Employee ID 125 and related details will successfully add.<\/p>\n<p>Now, as per our trigger, we must get the information of this new record entry in the <strong>DTFAUDIT<\/strong> table.<\/p>\n<p>In this table, you will get the timestamp and user name of the user who did the new entry in the <strong>DIMEMPLOYEE<\/strong> table. This shows that the trigger worked successfully.<\/p>\n<h3>Summary<\/h3>\n<p>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.<\/p>\n<p>Any queries or feedback for us? Do share your thoughts in the comment section.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#46;&#46;&#46;<\/p>\n","protected":false},"author":6,"featured_media":56861,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18002],"tags":[19870,19872,19871],"class_list":["post-56830","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sap-hana","tag-sap-hana-sql-triggers","tag-sql-triggers-creation-in-sap-hana","tag-sql-triggers-uses-in-sap-hana"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Create SQL Triggers in SAP HANA - The Process that you can&#039;t ignore! - DataFlair<\/title>\n<meta name=\"description\" content=\"Learn the significance of SQL triggers in SAP HANA, uses of SQL triggers, syntax &amp; syntax elements and process to create SQL triggers in SAP HANA studio.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create SQL Triggers in SAP HANA - The Process that you can&#039;t ignore! - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Learn the significance of SQL triggers in SAP HANA, uses of SQL triggers, syntax &amp; syntax elements and process to create SQL triggers in SAP HANA studio.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-25T09:11:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-04T07:30:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Triggers-in-SAP-HANA.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"801\" \/>\n\t<meta property=\"og:image:height\" content=\"420\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Create SQL Triggers in SAP HANA - The Process that you can't ignore! - DataFlair","description":"Learn the significance of SQL triggers in SAP HANA, uses of SQL triggers, syntax & syntax elements and process to create SQL triggers in SAP HANA studio.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/","og_locale":"en_US","og_type":"article","og_title":"Create SQL Triggers in SAP HANA - The Process that you can't ignore! - DataFlair","og_description":"Learn the significance of SQL triggers in SAP HANA, uses of SQL triggers, syntax & syntax elements and process to create SQL triggers in SAP HANA studio.","og_url":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2019-05-25T09:11:21+00:00","article_modified_time":"2021-12-04T07:30:30+00:00","og_image":[{"width":801,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Triggers-in-SAP-HANA.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Create SQL Triggers in SAP HANA &#8211; The Process that you can&#8217;t ignore!","datePublished":"2019-05-25T09:11:21+00:00","dateModified":"2021-12-04T07:30:30+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/"},"wordCount":878,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Triggers-in-SAP-HANA.jpg","keywords":["SAP HANA SQL Triggers","SQL Triggers Creation in SAP HANA","SQL Triggers Uses in SAP HANA"],"articleSection":["SAP HANA Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/","url":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/","name":"Create SQL Triggers in SAP HANA - The Process that you can't ignore! - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Triggers-in-SAP-HANA.jpg","datePublished":"2019-05-25T09:11:21+00:00","dateModified":"2021-12-04T07:30:30+00:00","description":"Learn the significance of SQL triggers in SAP HANA, uses of SQL triggers, syntax & syntax elements and process to create SQL triggers in SAP HANA studio.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Triggers-in-SAP-HANA.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Triggers-in-SAP-HANA.jpg","width":801,"height":420,"caption":"SQL Triggers in SAP HANA Topics"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/create-sql-triggers-in-sap-hana\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAP HANA Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sap-hana\/"},{"@type":"ListItem","position":3,"name":"Create SQL Triggers in SAP HANA &#8211; The Process that you can&#8217;t ignore!"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/56830","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=56830"}],"version-history":[{"count":13,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/56830\/revisions"}],"predecessor-version":[{"id":104889,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/56830\/revisions\/104889"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/56861"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=56830"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=56830"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=56830"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}