Oracle 在触发器中删除之前检查存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/373421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle checking existence before deletion in a trigger
提问by Prashant
I have analyzed a hibernate generated oracle database and discovered that a delete of a row from a single table will spawn the firing of 1200+ triggers in order to delete the related rows in child tables. The triggers are all auto-generated the same - an automatic delete of a child row without checking for existence first. As it is impossible to predict which child tables will actually have related rows, I think a viable solution to preventing the firing of the cascaded delete down a deeply branched completely empty limb, would be to check for the existence of a related row before attempting to delete. In other dbms', I could simply state " if exists....." before deleting. Is there a comparable way to do this in oracle?
我分析了一个休眠生成的 oracle 数据库，发现从单个表中删除一行将引发 1200 多个触发器的触发，以删除子表中的相关行。触发器都是自动生成的 - 自动删除子行而不先检查是否存在。由于无法预测哪些子表实际上将具有相关行，因此我认为防止在深分支的完全空分支下触发级联删除的可行解决方案是在尝试之前检查相关行的存在删除。在其他 dbms 中，我可以在删除之前简单地说明“如果存在.....”。在 oracle 中是否有类似的方法可以做到这一点？
回答by Gary Myers
"delete of a row from a single table will spawn the firing of 1200+ triggers" Are these statement or row level triggers ? If the latter, they'll only fire if a row is deleted. Say you have a BEFORE DELETE trigger on customers to delete the customers orders, and a BEFORE DELETE trigger on orders to delete order items. If the customer has no orders, and the orders table trigger is a row level trigger, then it will not fire the delete from order items.
“从单个表中删除一行将引发 1200 多个触发器的触发”这些是语句级触发器还是行级触发器？如果是后者，它们只会在删除一行时触发。假设您在客户上有一个 BEFORE DELETE 触发器来删除客户订单，在订单上有一个 BEFORE DELETE 触发器来删除订单项目。如果客户没有订单，并且订单表触发器是行级触发器，则不会触发从订单项中删除。
"check for the existence of a related row before attempting to delete" Probably no benefit. In fact it would do more work having a SELECT followed by a DELETE.
“在尝试删除之前检查相关行的存在”可能没有好处。事实上，它会做更多的工作，有一个 SELECT 后跟一个 DELETE。
Of course the Hibernate logic is broken. The deleting session will only see (and try to delete) committed transactions. If FRED has inserted an order for the customer, but it is not committed, JOHN's delete (through the trigger) won't see it or try to delete it. It will however still 'succeed' and try to delete the parent customer. If you have actually got your foreign key constraints enabled in the database, Oracle will kick in. It will wait until FRED commits, then reject the delete as it has a child. If the foreign key constraints aren't in place, you have an order for a non-existent customer. This is why you should have this sort of business logic enforced in the database.
当然，Hibernate 逻辑被破坏了。删除会话只会看到（并尝试删除）已提交的事务。如果 FRED 为客户插入了一个订单，但没有提交，JOHN 的删除（通过触发器）将不会看到它或尝试删除它。然而，它仍然会“成功”并尝试删除父客户。如果您确实在数据库中启用了外键约束，Oracle 将启动。它会等到 FRED 提交，然后拒绝删除，因为它有一个子项。如果外键约束没有到位，您就有一个不存在的客户的订单。这就是为什么您应该在数据库中强制实施此类业务逻辑的原因。
回答by Brian Schmitt
If possible, modify and setup your DB tables appropriately. - Involve a DBA if you have one at your disposal.
如果可能，请适当修改和设置您的数据库表。- 如果您有 DBA，请让 DBA 参与。
You need to use Foreign Key constraints and cascade deletes. This eliminates the need for triggers, etc...
您需要使用外键约束和级联删除。这消除了对触发器等的需要......
回答by Prashant
select * from Tab where Tname = "TABLENAME"
If this query returns any row then the table exists otherwise it doesn't.
如果此查询返回任何行，则该表存在，否则不存在。
回答by Ricardo Villamil
You can query the special dba_objects table: ?
您可以查询特殊的 dba_objects 表：
DECLARE
X NUMBER;
??BEGIN
SELECT COUNT(*) INTO X FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND OBJECT_NAME = 'YOUR_TRIGGER_NAME_HERE';
IF X = 0 THEN
--Trigger doesn't exist, OK to delete...
END IF;
END;
