Home
AS400 to IBM i
SQL

INSTEAD OF SQL Trigger (Adding / Deleting / Inserting record in the table through SQL View)

INSTEAD OF SQL Trigger (Adding / Deleting / Inserting record in the table through SQL View)
INSTEAD OF SQL Trigger (Adding/Deleting/Inserting record in the table through SQL View), INSTEAD OF SQL TRIGGER, TRIGGER ON SQL VIEW, DB2 IBM DB2, SQL, db2 for i SQL, create trigger, trigger, sql trigger, as400, ibmi, iseries, as400 tutorial,triggers in sql db2 for IBM i
INSTEAD OF SQL Trigger (Adding/Deleting/Inserting record in the table through SQL View)

Introduction

This article will explain the use of INSTEAD OF SQL triggers. There are some limitations present for INSTEAD OF SQL trigger.
  • Allowed on SQL View only
  • Not allowed on DDS logical files
  • Only one INSTEAD OF allowed for each SQL event
  • WHEN clause can't be used
  • FOR EACH STATEMENT can't be used

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

Table 1

Create two tables with the same structure.
A          R RTGPF9                
A            STUID          9P 0   
A            STUCLASS       2P 0   
A            STUSTATUS     10A     
A            STUFEES        9P 0

Table 2

Create two tables with the same structure.
A          R RTGPF9              
A            STUID          9P 0 
A            STUNAME       20A   
A            STUDOB          L   
A            STUADDR       50A

View on Table 1 and Table 2

CREATE VIEW TRGPFVIEW AS      
(SELECT A.STUID,               
        A.STUCLASS,            
        A.STUSTATUS,           
        A.STUFEES,             
        B.STUNAME,             
        B.STUDOB,              
        B.STUADDR              
   FROM TABLE1 A JOIN TABLE2 B
   ON A.STUID = B.STUID);

Trigger Execution

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

Trigger Condition

  • When a record is inserted
  • When a record is deleted
  • When a record is changed except the STUID file field.

Trigger Action

The record will be added/deleted/inserted in the underlying tables through View.

Example: INSTEAD OF SQL Trigger

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

CREATE OR REPLACE TRIGGER INSTEADOFTRIGGERDEMO                     
INSTEAD OF                                                         
INSERT OR UPDATE OR DELETE ON TRGPF9VIEW                           
REFERENCING NEW ROW AS NEW OLD ROW AS OLD                          
FOR EACH ROW MODE DB2ROW                                           
PROGRAM NAME TRIGGER13                                             
BEGIN ATOMIC                                                       
  IF INSERTING THEN                                                
    INSERT INTO TRGPF9 (STUID,STUCLASS,STUSTATUS,STUFEES)          
       VALUES(NEW.STUID, NEW.STUCLASS, NEW.STUSTATUS, NEW.STUFEES);
                                                                   
    INSERT INTO TRGPF9A (STUID,STUNAME,STUDOB,STUADDR)             
       VALUES(NEW.STUID, NEW.STUNAME, NEW.STUDOB, NEW.STUADDR);    
  END IF;                                                          
  IF UPDATING THEN                                                 
    IF NEW.STUID <> OLD.STUID THEN                                 
      SIGNAL SQLSTATE 'ERROR'                                      
      SET MESSAGE_TEXT =                                           
         'STUID CAN NOT BE UPDATED'; 
    END IF;                                                     
    UPDATE TRGPF9 SET STUCLASS = NEW.STUCLASS,                  
          STUSTATUS = NEW.STUSTATUS, STUFEES = NEW.STUFEES      
             WHERE STUID = OLD.STUID;                           
                                                                
    UPDATE TRGPF9A SET STUNAME = NEW.STUID, STUDOB = NEW.STUDOB,
          STUADDR =NEW.STUADDR                                  
             WHERE STUID = OLD.STUID;                           
  END IF;                                                       
  IF DELETING THEN                                              
    DELETE FROM TRGPF9 WHERE STUID = OLD.STUID;                 
    DELETE FROM TRGPF9A WHERE STUID = OLD.STUID;                
  END IF;                                                       
END;

The above-shown trigger will be executed only when the record is added or deleted or updated in View. 

Use of INSTEAD OF 
INSTEAD OF                                
INSERT OR UPDATE OR DELETE ON TRGPF9VIEW
The below statement is fetching the current row and the old row values 
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.

ATOMIC is used with BEGIN so that if any action failed then all the actions should be rolled back
BEGIN ATOMIC
Error handling code
IF NEW.STUID <> OLD.STUID THEN  
  SIGNAL SQLSTATE 'ERROR'       
  SET MESSAGE_TEXT =            
     'STUID CAN NOT BE UPDATED';
END IF;
Condition executed when a record is inserted in the table
IF INSERTING THEN                                                  
  INSERT INTO TRGPF9 (STUID,STUCLASS,STUSTATUS,STUFEES)            
     VALUES(NEW.STUID, NEW.STUCLASS, NEW.STUSTATUS, NEW.STUFEES);  
                                                                   
  INSERT INTO TRGPF9A (STUID,STUNAME,STUDOB,STUADDR)               
     VALUES(NEW.STUID, NEW.STUNAME, NEW.STUDOB, NEW.STUADDR);      
END IF;
Condition executed when a record is deleted in the table
IF DELETING THEN                               
  DELETE FROM TRGPF9 WHERE STUID = OLD.STUID;  
  DELETE FROM TRGPF9A WHERE STUID = OLD.STUID; 
END IF;
Condition executed when a record is updated except the STUID file field
IF UPDATING THEN                                              
  IF NEW.STUID <> OLD.STUID THEN                              
    SIGNAL SQLSTATE 'ERROR'                                   
    SET MESSAGE_TEXT =                                        
       'STUID CAN NOT BE UPDATED';                            
  END IF;                                                     
  UPDATE TRGPF9 SET STUCLASS = NEW.STUCLASS,                  
        STUSTATUS = NEW.STUSTATUS, STUFEES = NEW.STUFEES      
           WHERE STUID = OLD.STUID;                           
                                                              
  UPDATE TRGPF9A SET STUNAME = NEW.STUID, STUDOB = NEW.STUDOB,
        STUADDR =NEW.STUADDR                                  
           WHERE STUID = OLD.STUID;                           
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

Read also :

Post a Comment

© ‧ AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain