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

Fixed, /Free and Fully Free RPG


Self Referencing SQL trigger

Self Referencing SQL trigger
Self Referencing SQL trigger, sql trigger, trigger, create trigger, introduction , sql, ibmi, db2, as400, iseries, db2 for i sql, sl for ibmi db2,
Self Referencing SQL trigger

Introduction

This article will explain the self-referencing SQL Trigger

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

Table 1

Create tables on which trigger is attached
A          R RTGPF8              
A            EMPNO          9P 0 
A            EMPNAME       20A   
A            EMPROLE       15A   
A            MANAGERNO      9P 0 
A            SALARY         9P 0 
A            COMMISSION     9P 0 
A            DEPARTNO       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

Comparison between the old and new commission

Trigger Action

Table1 will be updated according to the condition

Example: Self Referencing SQL Trigger

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

CREATE OR REPLACE TRIGGER SELFREFERENCINGTRIGGER                    
AFTER UPDATE OF COMMISSION ON TRGPF8                                
REFERENCING NEW ROW AS NEW OLD ROW AS OLD                           
FOR EACH ROW MODE DB2ROW                                            
PROGRAM NAME TRIGGER12                                              
WHEN (NEW.COMMISSION <> OLD.COMMISSION AND                          
    NEW.EMPROLE <> 'MANAGER' AND NEW.EMPROLE <> 'DIRECTOR')         
BEGIN                                                               
 DECLARE L_MANAGERNO DECIMAL(9,0);                                  
 DECLARE L_MANAGERCOMMISSION DECIMAL(9,0);                          
  SET L_MANAGERNO = (SELECT MANAGERNO FROM TRGPF8 WHERE             
      EMPNO = NEW.EMPNO);                                           
  IF L_MANAGERNO IS NOT NULL THEN                                   
    SET L_MANAGERCOMMISSION = (NEW.COMMISSION - OLD.COMMISSION) ;   
    UPDATE TRGPF8 SET COMMISSION = COMMISSION + L_MANAGERCOMMISSION 
    WHERE EMPNO = L_MANAGERNO;                                      
  END IF;                                                           
END;                                                                                                                                                                                                                                                                                                                                               

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

The below statement will create the transition rows. New will be having the new row data and Old will be having the old row data.
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
When condition to start the execution
WHEN (NEW.COMMISSION <> OLD.COMMISSION AND                 
    NEW.EMPROLE <> 'MANAGER' AND NEW.EMPROLE <> 'DIRECTOR')
BEGIN / END is used to execute the multiple events, also IF/END IF is a condition operator to execute the action when a condition is true.

Selecting the manager and commission
SET L_MANAGERNO = (SELECT MANAGERNO FROM TRGPF8 WHERE
    EMPNO = NEW.EMPNO); 
SET L_MANAGERCOMMISSION = (NEW.COMMISSION - OLD.COMMISSION) ;                                                                                                                  
Condition to update the commission to the manager in Table 1 
IF L_MANAGERNO IS NOT NULL THEN                                                                                                                      
Table1 update statement
UPDATE TRGPF8 SET COMMISSION = COMMISSION + L_MANAGERCOMMISSION 
WHERE EMPNO = L_MANAGERNO;                                                                                                                                                                                                                                                          

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

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