Oracle checking existence before deletion in a trigger

Question

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?

Answer 1

You can query the special 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;

Answer 2

If possible, modify and setup your DB tables appropriately. - Involve a DBA if you have one at your disposal.

You need to use Foreign Key constraints and cascade deletes. This eliminates the need for triggers, etc...

Answer 3

"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.

"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.

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.

Answer 4

**select * from Tab where Tname = "TABLENAME"

<

If this query returns any row then table exist else Not**