DB2相当于SQL Server的TRIGGER_NESTLEVEL（）？ [英] DB2 equivalent of SQL Server's TRIGGER_NESTLEVEL()?

问题描述

我试图通过DB2（v9.7）控制一个递归触发器，不幸的是，IBM文档没有提到一种方式来知道当前触发器调用在哪个级别的递归。

我发现sql-server上有这个功能： trigger_nestlevel（） ，它基本上是我想要的（知道实际的触发器递归调用级别）。所以我想知道DB2中是否有等效的功能。

解决方案

不幸的是，DB2没有任何功能或存储过程为了这。
您最多可以有16个触发级联级别。

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

$ b $在DB2中的BTW，如果触发器导致另一个触发器被触发，则称为触发器级联。您可以避免NO CASCADE关键字的级联。

如果您想避免对同一触发器的调用，则有一个解决方法：

1. 为表创建一个视图=>从Table1创建View vTable1作为Select *


2. 为vTable1创建一个INSTEAD OF TRIGGER。 => CREATE TRIGGER TRG_INSERT INSERTAD OF INSERT ON vTable1


3. 在TRG_INSERT中插入到table1。

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.

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.

解决方案

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.

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 a view for your table => Create View vTable1 as Select * from Table1
2. Create an INSTEAD OF TRIGGER for vTable1. => CREATE TRIGGER TRG_INSERT INSTEAD OF INSERT ON vTable1
3. In TRG_INSERT, to insert into to table1.

这篇关于DB2相当于SQL Server的TRIGGER_NESTLEVEL（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！