Conditional SQL Trigger Example

Conditional SQL Trigger Example

Introduction

This article will explain a conditional SQL trigger example.

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 updated in the table. 

Trigger Condition

When the value of the COLLEGE  field from TABLE1 gets changed.

Trigger Action

Only when the trigger condition is true, only the STUCNT field from TABLE2 gets increment by 1.

Example: Conditional Trigger

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

CREATE OR REPLACE TRIGGER UPDATE_STUDENT   
AFTER UPDATE ON TRGPF1                     
REFERENCING NEW ROW AS NEW OLD ROW AS OLD  
FOR EACH ROW MODE DB2ROW                   
WHEN(NEW.COLLEGE <> OLD.COLLEGE)           
BEGIN                                      
UPDATE TRGPF2 SET STUCNT = STUCNT + 1;     
END;                                        

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

REFERENCING NEW ROW AS NEW OLD ROW AS OLD 

And, below is the condition that is comparing the old COLLEGE field value and the new COLLEGE field value.

WHEN(NEW.COLLEGE <> OLD.COLLEGE) 

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
• OF Clause in SQL Trigger
• Multiple Event Execution Through 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