DB2 update and insert trigger, references a lot of

Question

DB2 on iSeries is a little different then DB2 than other platforms but like I said it might not matter.

Here are my following triggers (which work) (One difference is the use of '/' vs '.' depending on what tool is used to create the query)

create trigger utbachInsert after insert on CO99PR/UTBACH  
 referencing new as n                                               
 for each row mode db2sql                                           
 begin atomic   
    insert into CO99PRH/UTBACH values(
 n.BCGRP, n.BCID,n.BCSTAT,n.BCDESC,n.YYRGDT,      
 n.MMRGDT,n.DDRGDT,n.YY1EDT,n.MM1EDT,n.DD1EDT,    
 n.YYBLDT,n.MMBLDT,n.DDBLDT,n.YYPSDT,n.MMPSDT,    
 n.DDPSDT,n.YYPGDT,n.MMPGDT,n.DDPGDT,n.BCCOMM,    
 n.BCUSER,n.YYDATE,n.MMDATE,n.DDDATE    
);                                                                                          
end 

create trigger utbachUpdate after update on CO99PR/UTBACH  
 referencing new as n                                               
 for each row mode db2sql                                           
 begin atomic   
    update CO99PRH/UTBACH set 
        BCGRP = n.BCGRP, 
        BCID = n.BCID,
        BCSTAT = n.BCSTAT,
        BCDESC = n.BCDESC,
        YYRGDT = n.YYRGDT,      
        MMRGDT = n.MMRGDT,
        DDRGDT = n.DDRGDT,
        YY1EDT = n.YY1EDT,
        MM1EDT = n.MM1EDT,
        DD1EDT = n.DD1EDT,    
        YYBLDT = n.YYBLDT,
        MMBLDT = n.MMBLDT,
        DDBLDT = n.DDBLDT,
        YYPSDT = n.YYPSDT,
        MMPSDT = n.MMPSDT,    
        DDPSDT = n.DDPSDT,
        YYPGDT = n.YYPGDT,
        MMPGDT = n.MMPGDT,
        DDPGDT = n.DDPGDT,
        BCCOMM = n.BCCOMM,    
        BCUSER = n.BCUSER,
        YYDATE = n.YYDATE,
        MMDATE = n.MMDATE,
        DDDATE = n.DDDATE;                                                  
end 

Simply in the above blocks I need to type a lot, I'm pretty sure I've used a select statement to get the entries I need for an other insert trigger and because it was a select statement I could use table1.* (it joined another table). Since n references the old row I was hoping I could say n.* or something like that.

I'll need to do this in a lot of places if you know it can not be done, I'll gladly accept that as an answer.

PS: Sometimes it helps to have context, I'm doing this for a set of tables to keep their contents in sync (well in one direction). The tables are used by programs which use record level access (before SQL was used on DB2) and changing the tables often means recompiling the programs which make use of them (dropping or adding a row, adding a referential constraint, and even adding a trigger which modifies the data from such a program and places it in the SAME table has been shown to cause issues and the system admin does not want to recompile these programs), so all the table values must be copied to the new tables, these tables can then be used as we'd expect without having much impact. This allow a certain amount of decoupling and lets us have some breathing room with regard to how we may manage the schema.

Answer 1

It's never a good idea to use the SELECT * format in queries (except ad-hoc dev-run, or with views - which should be considered the same as an API).
Especially when moving data from one file to another - what happens if you change the origin file, but don't change (deliberately or accidentally) the destination file?
I'm not aware of anything where adding/removing a trigger from a physical file causes level-check errors (meaning the program would need to be recompiled). If you change the definition of the file, and thus need to change the trigger, you'd need to recompile program anyways (because that does change the signature).

If you guys are really interested in being able to update the underlying schema, the preferred method is defining views (not replicated tables), and querying the views with SQL. Doing so will completely (as far as I'm aware) remove the need for RPG/RPGLE programs to be recompiled with PF changes (although changing a view will mean that things need to be updated...)