Change Row Before Inserting In Table (Before SQL Trigger)

Change Row Before Inserting In Table
Change Row Before Inserting In Table, before trigger, example, before sql trigger, sql trigger, trigger, as400, ibmi, db2, db2 for i sql, sql, sql programming, iseries
Change Row Before Inserting In Table (Before SQL Trigger)

Introduction

This article will cover the changing of the columns in the current row before inserting them into the Table.

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

Table1 

A          R RTGPF3               
A            ROLLNO         9P 0  
A            NAME          10A    
A            COLLEGE       50A    
A          K ROLLNO                          

Trigger Execution

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

Trigger Action

  • Name file field will be converted to the UPPER case.
  • COLLEGE file field value will be Trimmed, converted to the UPPER case, and decimal(.) is added at last.

Example: Before Trigger

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

CREATE OR REPLACE TRIGGER SET_VALUES_TRIGGER             
BEFORE INSERT ON TRGPF3                                  
REFERENCING NEW ROW AS NEW                               
FOR EACH ROW MODE DB2ROW                                 
BEGIN                                                    
  SET NEW.NAME = UPPER(NEW.NAME);                        
  SET NEW.COLLEGE = TRIM(UPPER(NEW.COLLEGE)) CONCAT '.'; 
END;                                                                                    

The above-shown trigger will be executed when the record is inserted in the table.

The below statement is fetching the current row 
REFERENCING NEW ROW AS NEW 
The trigger is executed Before inserting the row into the table.
BEFORE INSERT ON TRGPF3 
And, below action will change the current column values.
SET NEW.NAME = UPPER(NEW.NAME);                        
SET NEW.COLLEGE = TRIM(UPPER(NEW.COLLEGE)) CONCAT '.'; 

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