Transition Tables in SQL Triggers

Transition Tables in SQL Triggers
Transition Tables in SQL Triggers sql trigger, trigger, create, introduction, about, what, what is, db2 for i sql, ibmi, as400, iseriees, sql , sql programming
Transition Tables in SQL Triggers

Introduction

This article will explain the transition table created during the execution of triggers and one can use them in their business logic.

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

Table 1

Create tables on which trigger is attached
A          R RTGPF6             
A            NUMBER         3P 0            

Table 2

Create tables that get updated when the trigger gets executed
A          R RTGPF7             
A            TOTAL          9P 0
A            REMARKS       50A                                                                

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 of total value in Old table and New table

Trigger Action

Record is inserted in Table 2 according to the condition (UPDATE -INCREMENT / UPDATE-DECREMENT)

Example: Trigger With Transition Table

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

CREATE OR REPLACE TRIGGER USINGTRANSITIONTABLE                                 
AFTER UPDATE OF NUMBER ON TRGPF6                                               
REFERENCING NEW TABLE AS NEWTABLE OLD TABLE AS OLDTABLE                        
FOR EACH STATEMENT MODE DB2SQL                                                 
PROGRAM NAME TRIGGER10                                                         
BEGIN                                                                          
DECLARE OLD_TOTAL DECIMAL(9,0);                                                
DECLARE NEW_TOTAL DECIMAL(9,0);                                                
                                                                               
    SELECT SUM(NUMBER) INTO OLD_TOTAL FROM OLDTABLE;                           
    SELECT SUM(NUMBER) INTO NEW_TOTAL FROM NEWTABLE;                           
    IF NEW_TOTAL > OLD_TOTAL THEN                                              
       INSERT INTO TRGPF7(TOTAL,REMARKS) VALUES(NEW_TOTAL,'UPDATE -INCREMENT');
    ELSE                                                                       
       INSERT INTO TRGPF7(TOTAL,REMARKS) VALUES(NEW_TOTAL,'UPDATE-DECREMENT'); 
    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 tables. NewTable will be having the data that will be after the update and OldTable will be having the data that was present before the update
REFERENCING NEW TABLE AS NEWTABLE OLD TABLE AS OLDTABLE
Work variable used in the trigger
DECLARE OLD_TOTAL DECIMAL(9,0);
DECLARE NEW_TOTAL DECIMAL(9,0);
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.

Fetch the total sum from the old transition table
SELECT SUM(NUMBER) INTO OLD_TOTAL FROM OLDTABLE;                                                                                    
Fetch the total sum from the new transition table
SELECT SUM(NUMBER) INTO NEW_TOTAL FROM NEWTABLE;                                                                                                                     
Condition to insert the record in Table 2 and the insert queries
IF NEW_TOTAL > OLD_TOTAL THEN                                              
   INSERT INTO TRGPF7(TOTAL,REMARKS) VALUES(NEW_TOTAL,'UPDATE -INCREMENT');
ELSE                                                                       
   INSERT INTO TRGPF7(TOTAL,REMARKS) VALUES(NEW_TOTAL,'UPDATE-DECREMENT'); 
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