Now, Get RPG Codes in all three formats only on this website on every RPGLE related programming article.

Fixed, /Free and Fully Free RPG


OF Clause in SQL Trigger

OF Clause in SQL Trigger
OF Clause in SQL Trigger, sql trigger, trigger, db2, db2 for i sql, ibmi, as400, iseries, sql, sql programming
OF Clause in SQL Trigger

Introduction

This article will explain the working of OF clause in the SQL trigger and how to execute the trigger depending on the column and on complete row change.

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: OF Clause Trigger

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

CREATE OR REPLACE TRIGGER UPDATE_STUDENT_COLLEGE 
AFTER UPDATE OF COLLEGE 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 above-shown trigger will be executed only when the COLLEGE file field gets updated due to  OF clause on the file field COLLEGE/. It will not get executed if any other field from the file gets updated.
AFTER UPDATE OF COLLEGE ON TRGPF1 
 
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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain