PL / SQL尝试通过同一个表上的触发器更新表 [英] PL/SQL Trying to update a table through trigger on the same table

问题描述

我坚持这个问题，我找不到溶剂。我将尝试逐步解释。


1某些行更新后会显示某个值。我事先并不知道。并且它们没有特定的顺序，但在更新后它们都具有相同的trans_id。 （在unload_details_tab中）。

2我选择使用刚刚更新的值（trans_id）选择行，这样我就知道需要选择哪些行，所有行的值都相同我需要。

3然后我调用一个为每一行生成block_id的过程，我用一个表来存储这个block_id。 （temptable）


这一切都很精细。


4但问题是我想用temptable中的值更新unload_details_tab。更新后触发器会产生自死锁错误。

我也不能使用beofre更新，因为我需要所有具有相同trans_id的行来生成block_id。此外，你可能会认为这会导致递归触发器触发，但是我首先检查block_id是否为空，这样如果它被填充，则触发器不再循环。但为了实现这一点，表格需要首先更新值，这不会发生。我相信更新后的触发器会锁定unload_details_tab，因此我无法再次更新它。我不是100％舒服。



简短：


更新后，我创建了一个需要的blok_id更新到相同的行，首先在同一个表格中更新。


我在我的智慧结束，并没有看到明确的解决方案对于这个问题，请以任何方式帮助。


提前谢谢。

Hi,
I''m stuck on this problem and I can''t find a sollution. I''m going to try and explain this step by step.


1 After certain rows get updated with a certain value. I don''t know wich rows in advance. And they do not have a certain order, but after the update they all have the same trans_id. (in unload_details_tab).
2 I select select the rows using the value that just got updated (trans_id), this way I know wich rows I needed to select, the value is the same for all the rows that I need.
3 I then call a procedure that generates a block_id for each row, I use a table to store this block_id. (temptable)

THIS ALL WORKS FINE.

4 But the problem is that I want to update the unload_details_tab with the values from temptable. The after update trigger generates a self-deadlock error.
I also can''t use a beofre update either becasue I need all of the rows with the same trans_id to generate the block_id. Also, you might think that this leads to a recursive trigger firing itself, but I check to see if the block_id is empty in the first place, this way if it is filled the trigger doesn''t loop again. But for this to work the table needs to get updated with the values in the first place and this doesn''t happen. I believe that the after update trigger locks the unload_details_tab, so i can''t update it again. I''m not 100% shure.


short:

After an update, I create a blok_id that needs to be updated to the same rows that got updated in the first place, in the same table ofcourse.

I''m at my wits end, and don''t see a clear sollution for this problem, please help any way you can.

Thanks in advance.

推荐答案

你背后的原因是什么获取每一行的block_id并在临时表中输入并使用相同的方法来更新从中获得此块ID的表？

What is the reason behind you getting the block_id for each row and sotring in temp table and using the same to update the table from which you got this block id?

你为每一行获取block_id并在临时表中输入并使用相同的方法来更新你获得此块ID的表的原因是什么？

What is the reason behind you getting the block_id for each row and sotring in temp table and using the same to update the table from which you got this block id?

我想你错过了我的想法，生成了block_id，这就是得到更新的内容。 block_id当时是未知的，它是在使用trans_id更新行之后生成的。喔，感谢快速回复......

You missunderstood me i think, the block_id gets generated and that is what gets updated back. The block_id is not known at that time, it gets generated after the update of the rows with a trans_id. Ow, and thanks for the fast reply...

我想你了解了我，生成了block_id，这就是更新的内容背部。 block_id当时是未知的，它是在使用trans_id更新行之后生成的。喔，感谢快速回复...

You missunderstood me i think, the block_id gets generated and that is what gets updated back. The block_id is not known at that time, it gets generated after the update of the rows with a trans_id. Ow, and thanks for the fast reply...

你正在做的第一次更新是手动完成的吗？

所以你手动更新，以及在该表上触发AFTER UPDATE触发器的COMMIT调用另一个过程来生成block_id吗？

The first update that you are doing is done manually is it?
so you do manual update, and COMMIT which triggers a AFTER UPDATE trigger on that table which calls another procedure to generate block_id?

这篇关于PL / SQL尝试通过同一个表上的触发器更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！