DB2更新和插入触发器，我可以使用*来缩短很多字段的引用（在iSeries中，但这可能无关紧要） [英] DB2 update and insert trigger, references a lot of fields can I use * to shorten them (in iSeries but might not matter)

问题描述

iSeries上的DB2与其他平台上的DB2稍有不同，但就像我说的没关系。

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 

在上述代码中，我需要输入很多内容，很漂亮确保使用了select语句来获取其他插入触发器所需的条目，并且因为它是一条select语句，所以我可以使用table1。*（它连接了另一个表）。由于n引用了旧行，所以我希望我可以说n。*之类的。

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 ：有时有助于上下文，我这样做是为了使一组表保持其内容同步（以及一个方向）。这些表由使用记录级别访问的程序使用（在DB2上使用SQL之前），更改表通常意味着重新编译使用它们的程序（删除或添加行，添加引用约束，甚至添加触发器）。已表明修改此类程序中的数据并将其放在SAME表中会导致问题，并且系统管理员不希望重新编译这些程序），因此必须将所有表值都复制到新表中，这些表可以然后按照我们的预期使用，而不会产生太大影响。这允许一定程度的解耦，并让我们在如何管理模式方面有一些喘息的空间。

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.

推荐答案

在 SELECT * 格式中使用它永远不是一个好主意查询（临时dev-run或具有视图的视图除外-应该与API相同）。

特别是在将数据从一个文件移动到另一个文件时-如果更改原始文件会发生什么情况，但不要更改（有意或无意）目标文件？

我不知道在物理文件中添加/删除触发器会导致级别检查错误的任何情况（这意味着程序需要重新编译）。如果更改文件的定义，因此需要更改触发器，则无论如何都需要重新编译程序（因为确实更改了签名）。

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).

如果你们真的对能够更新基础架构感兴趣，则首选方法是定义视图（非复制表），并使用SQL查询视图。这样做（据我所知）将完全不需要使用PF更改重新编译RPG / RPGLE程序（尽管更改视图将意味着需要更新...）

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...)

这篇关于DB2更新和插入触发器，我可以使用*来缩短很多字段的引用（在iSeries中，但这可能无关紧要）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！