Clever Geek Handbook

Oracle check for existence before deleting in trigger

I analyzed the oracle database generated by the hibernator and found that deleting a row from one table would fire a 1200+ trigger to delete related rows in child tables. Triggers are all automatically generated the same way - automatic deletion of a child row without first checking for existence. Since it is impossible to predict which child tables will have associated rows, I think a viable solution to prevent a deeply ramified completely empty limb cascading deletion pounding should be to check for the existence of the corresponding row before trying to delete. In other dbms, I could just specify "if exists ....." before deleting. Is there a comparable way to do this in oracle?

0


source to share


4 answers


"deleting a row from one table will trigger 1200+ triggers" Are these triggers triggers or rows? If the latter, they will only fire if the line is removed. Say you have a DELETE trigger for customers to delete customer orders and a BEFORE DELETE trigger for orders to delete order items. If the customer has no orders, and the table of orders trigger is a row level trigger, it will not remove the deletion from the order items.

"check for a matching line before trying to delete" Probably no use. It would actually do more work with a SELECT followed by a DELETE.



Of course, Hibernate's logic is broken. The delete session will display (and attempt to delete) transactions. If FRED inserted an order for a customer, but it was not committed, JOHN delete (via a trigger) will not see it or try to delete it. However, it will still "succeed" and try to remove the parent client. If you actually got the foreign key constraints included in the database, Oracle will work. It will wait for FRED to commit and then reject the deletion since it has a child. If no foreign key constraints are set, you have an order for a non-existent customer. This is why you must have this kind of business logic applied in the database.

+4


source


If possible, change and adjust your database tables accordingly. - Engage a database administrator if you have one at your disposal.



You need to use foreign key constraints and cascading deletes . This removes the need for triggers, etc.

+2


source


You can query the custom dba_objects table:

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;
0


source 


select * from Tab where Tname = "TABLENAME"

If this query returns any row, then the table exists, otherwise it doesn't.

0


source






More articles:


All Articles