DB2 等效于 SQL Server 的 TRIGGER_NESTLEVEL()?

2022-01-14 00:00:00 sql db2 sql-server triggers

我正在尝试控制 DB2 (v9.7) 上的递归触发器，不幸的是，IBM 文档没有提到一种方法来了解当前触发器调用处于哪个递归级别.

I'm trying to get control of a recursive trigger over DB2 (v9.7), unfortunately IBM documentation doesn't mention a way to know at which level of recursion the current trigger call is in.

我发现sql-server上有这个功能:trigger_nestlevel()，它基本上做我想要的(知道实际触发递归调用级别).所以我想知道DB2中是否有等效的功能.

I've found that there's this function on sql-server: trigger_nestlevel(), it basically does what I want (knowing the actual trigger recursive call level). so I would like to know if there is an equivalent function in DB2.

推荐答案

不幸的是，DB2 没有为此提供任何函数或存储过程.您最多可以有 16 个触发级联级别.

Unfortunately DB2 does NOT have any function or stored procedure for this. You can have maximum of 16 trigger cascade levels.

它是硬编码的，不能更改.

It is hard coded and can not be changed.

顺便说一句，在 DB2 中，如果一个触发器导致另一个触发器被触发，则称为触发器级联.并且您可以通过 NO CASCADE 关键字避免级联.

BTW in DB2, if a trigger causes another trigger to be fired, it is called trigger cascade. And you can avoid cascading by NO CASCADE keyword.

如果你想避免调用同一个触发器，有一个解决方法:

If you want to avoid the call to same trigger, there is a workaround:

  1. 为您的表创建一个视图 => Create View vTable1 as Select * from Table1
  2. 为 vTable1 创建一个 INSTEAD OF TRIGGER.=> 创建触发器 TRG_INSERT 而不是在 vTable1 上插入
  3. 在 TRG_INSERT 中，插入到 table1.

相关文章