Error Handling in SQL Trigger Using Signalling

Error Handling in SQL Trigger Using Signalling
Error Handling in SQL Trigger Using Signalling, SQl trigger error handling, signal in SQL TRIGGER, sql trigger, trigger, create trigger, sql, sql programming, ibmi, as400, db2 for i sql, db2 ibmi
Error Handling in SQL Trigger Using Signalling

Introduction

This article will explain the error handling in SQL trigger using Signalling.

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            

Trigger Execution

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

Trigger Condition

The new value is greater than double the old value.

Trigger Action

The record will not get updated and an error will be displayed

Example: Error Handling Using Signalling

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

CREATE OR REPLACE TRIGGER SIGNALERRORTRIGGER                       
BEFORE UPDATE ON TRGPF6                                            
REFERENCING NEW ROW AS NEW OLD ROW AS OLD                          
FOR EACH ROW MODE DB2ROW                                           
PROGRAM NAME TRIGGER11                                             
BEGIN                                                              
  IF NEW.NUMBER > OLD.NUMBER *2 THEN                               
    SIGNAL SQLSTATE 'ERROR'                                        
    SET MESSAGE_TEXT = 'NUMBER MORE THAN DOUBLE NOT ALLOWED';      
  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 rows. New will be having the new row data and Old will be having the old row data.
REFERENCING NEW ROW AS NEW OLD ROW AS OLD
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.

Statement to modified SQLSTATE (SQLSTATE can be anything)
SIGNAL SQLSTATE 'ERROR'                                                                                    
Error to be displayed when the condition is fulfilled 
SET MESSAGE_TEXT = 'NUMBER MORE THAN DOUBLE NOT ALLOWED';                                                                                                                     
Error condition 
IF NEW.NUMBER > OLD.NUMBER *2 THEN                         
  SIGNAL SQLSTATE 'ERROR'                                  
  SET MESSAGE_TEXT = 'NUMBER MORE THAN DOUBLE NOT ALLOWED';
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