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.

Read also :

AS400 and SQL Tricks

Transition Tables in SQL Triggers

Introduction

Table 1

Table 2

Trigger Execution

Trigger Condition

Trigger Action

Example: Trigger With Transition Table

Related Post

Post a Comment

File information data structures (INFDS) in RPG AS400

Data Structure and Types of DS in RPG AS400

SQL stored procedure in IBM i