Stored Procedure calling in SQL Trigger

Stored Procedure calling in SQL Trigger
Stored Procedure calling in SQL Trigger,Stored Procedure calling in SQL Trigger in ibmi as400, call stored procedure in trigger in ibmi db2, db2 trigger, sql trigger, trigger, stored procedure, stored procedure in ibmi as400
Stored Procedure calling in SQL Trigger

Introduction

This article will explain the execution of stored procedures through SQL Trigger.

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

Table 

Create two tables with the same structure.
A          R RTGPF1             
A            ROLLNO         9P 0
A            NAME          10A  
A            COLLEGE       50A  
A          K ROLLNO            

Stored Procedure

CREATE OR REPLACE PROCEDURE SQLTRPROC1(                                         
        IN P_ROLLNO DECIMAL(9,0),                                               
        IN P_NAME CHAR(10),                                                     
        IN P_COLLEGE CHAR(50),                                                  
        IN P_FLAG CHAR(1)                                                       
         )                                                                      
SPECIFIC SQLTRPROC1                                                             
BEGIN                                                                         
 IF P_FLAG = 'I' THEN                                                         
  INSERT INTO TRGPF5(ROLLNO,NAME,COLLEGE) VALUES(P_ROLLNO, P_NAME ,P_COLLEGE);
 END IF;                                                                      
                                                                              
 IF P_FLAG = 'D' THEN                                                         
  DELETE FROM TRGPF5 WHERE ROLLNO = P_ROLLNO AND NAME = P_NAME AND            
        COLLEGE = P_COLLEGE;                                                  
 END IF;                                                                      
                                                                              
 IF P_FLAG = 'U' THEN                                                         
  UPDATE TRGPF5 SET  NAME = P_NAME WHERE ROLLNO = P_ROLLNO AND                
        COLLEGE = P_COLLEGE;                                                  
 END IF;                                                                      
END                                                                               

Trigger Execution

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

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

Stored procedure SQLTRPROC1 gets called with different parameters

Example: Trigger With Stored Procedure

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

CREATE OR REPLACE TRIGGER CALLPROCFROMTRIGGER            
AFTER INSERT OR DELETE OR UPDATE OF NAME ON TRGPF4       
REFERENCING NEW ROW AS NEW OLD ROW AS OLD                
FOR EACH ROW MODE DB2ROW                                 
PROGRAM NAME TRIGGER9                                    
BEGIN                                                    
DECLARE L_ROLLNO DECIMAL(9,0);                           
DECLARE L_NAME CHAR(10);                                 
DECLARE L_COLLEGE CHAR(50);                              
DECLARE L_FLAG CHAR(1);                                  
 IF INSERTING THEN                                       
    SET L_FLAG = 'I';                                    
    SET L_ROLLNO = NEW.ROLLNO;                           
    SET L_NAME = NEW.NAME;                               
    SET L_COLLEGE = NEW.COLLEGE;                         
   CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;                                                 
                                                        
IF DELETING THEN                                        
   SET L_FLAG = 'D';                                    
   SET L_ROLLNO = OLD.ROLLNO;                           
   SET L_NAME = OLD.NAME;                               
   SET L_COLLEGE = OLD.COLLEGE;                         
   CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;                                                 
                                                        
IF UPDATING AND NEW.NAME <> OLD.NAME THEN               
   SET L_FLAG = 'U';                                    
   SET L_ROLLNO = OLD.ROLLNO;                           
   SET L_NAME = NEW.NAME;                               
   SET L_COLLEGE = OLD.COLLEGE;                         
   CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
 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
And, below is the condition that is comparing the old COLLEGE field value and the new COLLEGE field value.
WHEN(NEW.COLLEGE <> OLD.COLLEGE)

Work variable used in the trigger
DECLARE L_ROLLNO DECIMAL(9,0);
DECLARE L_NAME CHAR(10);      
DECLARE L_COLLEGE CHAR(50);   
DECLARE L_FLAG CHAR(1);
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.

Condition executed when a record is inserted in the table
IF INSERTING THEN                                       
   SET L_FLAG = 'I';                                    
   SET L_ROLLNO = NEW.ROLLNO;                           
   SET L_NAME = NEW.NAME;                               
   SET L_COLLEGE = NEW.COLLEGE;                         
   CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;                                                                                    
Condition executed when a record is deleted in the table
IF DELETING THEN                                        
   SET L_FLAG = 'D';                                    
   SET L_ROLLNO = OLD.ROLLNO;                           
   SET L_NAME = OLD.NAME;                               
   SET L_COLLEGE = OLD.COLLEGE;                         
   CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
END IF;                                                                                                                     
Condition executed when a record is updated and the College field is changed in the table
IF UPDATING AND NEW.NAME <> OLD.NAME THEN               
   SET L_FLAG = 'U';                                    
   SET L_ROLLNO = OLD.ROLLNO;                           
   SET L_NAME = NEW.NAME;                               
   SET L_COLLEGE = OLD.COLLEGE;                         
   CALL SQLTRPROC1(L_ROLLNO, L_NAME, L_COLLEGE, L_FLAG);
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

Post a Comment

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