Change Row Before Inserting In Table (Before SQL Trigger)

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

Read also :

• SQL Triggers concept in IBM i DB2
• Syntax for Create Trigger in IBM i AS400
• Simple SQL Trigger Example
• Conditional SQL Trigger Example
• OF Clause in SQL Trigger
• Multiple Event Execution Through SQL Trigger
• Stored Procedure calling in SQL Trigger
• Transition Tables in SQL Triggers
• Error Handling in SQL Trigger Using Signalling
• Self Referencing SQL trigger
• INSTEAD OF SQL Trigger (Adding / Deleting / Inserting record in the table through SQL View)
• Trigger Limitations and Program Attributes
• Effects On Trigger When New File Field Is Added
• SYSTRIGGERS Catalog Table for SQL Trigger
• SYSTRIGDEP Catalog Table for SQL Trigger
• SYSTRIGUPD Catalog Table for SQL Trigger
• SYSTRIGCOL Catalog Table for SQL Trigger