SAP HANA : Triggers After

v  This statement is used to triggers which is a set of statements that are executed when a given operation (INSERT/UPDATE/DELETE) takes place on a given subject table or subject view.

v  A trigger is also a stored procedure that automatically executes when an event happens on a given table or view.

v  The database users only having the TRIGGER privilege for the given <subject_table_name> are allowed to create a trigger for that table or view.

The CREATE TRIGGER command defines a set of statements that are executed when a given operation (INSERT/UPDATE/DELETE) takes place on a given subject table or subject view. 

Creates a trigger on a table or view.

Syntax

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;

For more details refer this link: Triggers in Details

Example:

Here I have two table named as Product table and Sales table:

Product Table

Sales Table

Now, I’m Trying to create a trigger with the concept whenever I inserting a new record in a sales table the Product table should update is “Inventory” based on sales table  “quantity”

Code:

/*---------------------------------------------------------*/

create trigger "KABIL_PRACTICE"."SALES_TRIGGER"

after insert on "KABIL_PRACTICE"."SALES" REFERENCING NEW ROW AS newrow for each row

begin

update "KABIL_PRACTICE"."Inventory" set "Inventory" = "Inventory" - :newrow.QTY

where "P_ID" = :newrow.P_ID ;

end;

/*-----------------------------------------------------------*/

Now Insert a record in sales table:

Code:

insert into "KABIL_PRACTICE"."SALES" values(3,8,'Light',10,500);

Now the Sales table should be like this:

Result of Sales Table

Results:

             When I inserting a new record in my Sales Table the trigger will execute automatically. From the above insert query will insert light product with the quantity of 10. so, the Product table inventory value of that light product will be reduced as per the quantity ordered in the sales table.

Updated Product Table