Cascading Updates and Deletes in LANSA

Date:	Archived
Product/Release:	LANSA - All platforms
Abstract:	How to cascade updates and deletes in LANSA using Triggers
Submitted By:	LANSA Technical Support

There are a number of reasons why updates and deletes could be cascaded, for example :

If information that used in a key changes, it is usually necessary to cascade the changes to the related files
When deleting a record from a "header file" the related record(s) in the "detail " must be deleted before the header can be deleted.

LANSA has Triggers which can occur before update and delete. Therefore, a trigger function can be created to carry out a cascading update or delete.

Note: It is not possible to have a delete referential integrity check validation rule (for a cascade delete) or a change validation rule (for a cascade update). Also do not underestimate the differences in database validation design that it is necessary to understand.

Example File level Trigger

DC@P700103             Define/Change a Trigger

File     : LCCBOK     BOOK

Field    : LCCBID     Book ID Number

Order to process / source  . . . . .  10 / FILE DEFINITION

User description of trigger . . . . . Cascade Update and Delete

Trigger Function Name  . . .. . . . . CASC001

                                                                                        
                                             Before     After

Trigger Point(s) . . . . . . . . . .  Open

                                                                         
                                      Close

                                                                         
                                      Read

                                                                         
                                      Insert

                                                                         
                                      Update    Y
  
                                                                           
                                      Delete    Y



                     
              And/OrField          Op     Field/Literal
  
Trigger When . . . . .

Example RDML Function

* ********* Beginning of RDML commands ********************* 
=======================================================
* ********* Copyright .....: (C)       Aspect Computing, 1997
* ********* Type ..........: XXXXXX File Level Trigger 
* ********* Created on ....: 10/01/97 at 11:53:08 
* ********* Description ...: Trigger to Cascade Updates 
* *********                  and Deletes         
=======================================================
FUNCTION   OPTIONS(*DIRECT *NOMESSAGES *LIGHTUSAGE 
      *MLOPTIMISE) RC V_LIST(#TRIG_LIST) TRIGGER(*FILE XXXXXX)
* ********* =======================================================
* ********* Working fields, lists and groups 
======================================================
DEF_LIST   NAME(#TRIG_LIST) TYPE(*WORKING) ENTRYS(2) 
DEFINE     FIELD(#YYYYYYPRV) REFFLD(#YYYYYY)
DEFINE     FIELD(#YYYYYYNEW) REFFLD(#YYYYYY)
* ********* Function Mainline: CASC001
* ********* Assume a "good" return initially 
CHANGE     FIELD(#TRIG_RETC) TO('OK')
* *********
CASE       OF_FIELD(#TRIG_OPER)
* ********* Handle a before update event 
WHEN       VALUE_IS('= BEFUPD')
GET_ENTRY  NUMBER(1) FROM_LIST(#TRIG_LIST)
CHANGE     FIELD(#YYYYYYNEW) TO(#YYYYYY) 
GET_ENTRY  NUMBER(2) FROM_LIST(#TRIG_LIST)
CHANGE     FIELD(#YYYYYYPRV) TO(#YYYYYY)
IF         COND('(#YYYYYYNEW *NE #YYYYYYPRV)')
SELECT     FIELDS((#YYYYYY)) FROM_FILE(XXXXXX) 
           WITH_KEY(#YYYYYYPRV)
CHANGE     FIELD(#YYYYYY) TO(#YYYYYYNEW)
UPDATE     FIELDS((#YYYYYY)) IN_FILE(XXXXXX) 
           VAL_ERROR(*NEXT) 
IF_STATUS  IS(*VALERROR) 
EXECUTE    SUBROUTINE(ERROR)
ENDIF                       
ENDSELECT                   
ENDIF                       
* ********* Handle a before delete event
WHEN       VALUE_IS('= BEFDLT')
GET_ENTRY  NUMBER(1) FROM_LIST(#TRIG_LIST)
DELETE     FROM_FILE(XXXXXX) WITH_KEY(#YYYYYY) 
           VAL_ERROR(*NEXT)
IF_STATUS  IS(*VALERROR)    
EXECUTE    SUBROUTINE(ERROR)
ENDIF                       
* ********* Handle an event not catered for 
OTHERWISE 
ABORT      MSGTXT('File XXXXXX trigger function 
           invalidly invoked/used.') 
ENDCASE  
* ********* Return control to the invoker 
RETURN                      
SUBROUTINE NAME(ERROR)
DEFINE     FIELD(#ERRLINE) TYPE(*CHAR) LENGTH(100) 
           LABEL('Error.........')
DEF_HEAD   NAME(#HEAD01) FIELDS((#DATE) (#TIME) 
          (#USER) (#REP1PAGE) (#FUNCTION))                     
DEF_LINE   NAME(#LINE01) FIELDS((#ERRLINE)) 
           IDENTIFY(*LABEL) 
MESSAGE    MSGTXT('Error while doing cascade 
           update or delete')
USE        BUILTIN(GET_MESSAGE) TO_GET(#RETCOD #ERRLINE)
DOWHILE    COND('(#RETCOD = OK)')
PRINT      LINE(#LINE01)    
USE        BUILTIN(GET_MESSAGE) TO_GET(#RETCOD #ERRLINE) 
ENDWHILE   
CHANGE     FIELD(#TRIG_RETC) TO('VE') 
ENDROUTINE