Multiple Event Execution Through SQL Trigger

Multiple Event Execution Through SQL Trigger

Introduction

This article contains the SQL trigger that will be executed on multiple actions on the table.

Below are the table structures that will be used in the example

Table1 

A          R RTGPF1             
A            ROLLNO         9P 0
A            NAME          10A  
A            COLLEGE       50A  
A          K ROLLNO            

Table2

A          R RTGPF2              
A            STUCNT         9P 0 

Trigger Execution

The trigger will be added to TABLE1 and it will get triggered when any record is added or deleted or updated in TABLE1. 

Trigger Condition

• When a record is inserted
• When a record is deleted
• When a record is changed with the value of the COLLEGE  field from TABLE1 gets changed.

Trigger Action

• When a record is inserted in the table FILE1 then the STUCNT field from TABLE2 gets increment by 1.
• When a record is deleted from the table FILE1 then the STUCNT field from TABLE2 gets decrement by 1.
• When the Trigger Condition is true, only the STUCNT field from TABLE2 gets increment by 1.

Example: Multiple Event Execution In Trigger

In this example, the MULTIPLEEVENT_STUDENT trigger gets created and if already present then that will be replaced by this trigger.

CREATE OR REPLACE TRIGGER MULTIPLEEVENT_STUDENT       
AFTER INSERT OR DELETE OR UPDATE OF COLLEGE ON TRGPF1 
REFERENCING NEW ROW AS NEW OLD ROW AS OLD             
FOR EACH ROW MODE DB2ROW                              
BEGIN ATOMIC                                          
IF INSERTING THEN                                     
  UPDATE TRGPF2 SET STUCNT = STUCNT + 1;              
END IF;                                               
IF DELETING THEN                                      
  UPDATE TRGPF2 SET STUCNT = STUCNT - 1;              
END IF;                                               
IF UPDATING AND NEW.COLLEGE <> OLD.COLLEGE THEN       
  UPDATE TRGPF2 SET STUCNT = 0;                       
END IF;                                               
END;                                                                                   

The above-shown trigger will be executed only when the record is added or deleted or updated in TABLE1. 

 

The below statement is fetching the current row and the old row values 

REFERENCING NEW ROW AS NEW OLD ROW AS OLD 

BEGIN / END is used to execute the multiple events, also IF/END IF as a condition operator to execute the action when a condition is true.

Condition executed when a record is inserted in the table

IF INSERTING THEN                         
  UPDATE TRGPF2 SET STUCNT = STUCNT + 1;  
END IF;                                   

Condition executed when a record is deleted in the table

IF DELETING THEN                        
  UPDATE TRGPF2 SET STUCNT = STUCNT - 1;
END IF;                                                                    

Condition executed when a record is updated and the College field is changed in the table

IF UPDATING AND NEW.COLLEGE <> OLD.COLLEGE THEN  
  UPDATE TRGPF2 SET STUCNT = 0;                  
END IF;                                           

IF / ENF IF blocks can have multiple actions in them.

Trigger object is a CLE program object which can be debugged.

Related Post

Read also :

• SQL Triggers concept in IBM i DB2
• Syntax for Create Trigger in IBM i AS400
• Simple SQL Trigger Example
• Conditional SQL Trigger Example
• OF Clause in SQL Trigger
• Change Row Before Inserting In Table (Before SQL Trigger)
• Stored Procedure calling in SQL Trigger
• Transition Tables in SQL Triggers
• Error Handling in SQL Trigger Using Signalling
• Self Referencing SQL trigger
• INSTEAD OF SQL Trigger (Adding / Deleting / Inserting record in the table through SQL View)
• Trigger Limitations and Program Attributes
• Effects On Trigger When New File Field Is Added
• SYSTRIGGERS Catalog Table for SQL Trigger
• SYSTRIGDEP Catalog Table for SQL Trigger
• SYSTRIGUPD Catalog Table for SQL Trigger
• SYSTRIGCOL Catalog Table for SQL Trigger