CODEWITHSUNDEEP
sqloracle-databasetriggerscascade
TTK
TTK

Reputation: 223

Oracle: ON DELETE CASCADE causing trigger recursion

Let's take as example this two tables: Customer containing the customers and Product containing the products bought/used by the customers.

Every product references a customer trough the foreign key CustomerID, which corresponds to the primary key of the table Customer (they also have the same name).

When a customer is deleted all the products which referenced that costumer are deleted: Product.CustomerID has the attribute ON DELETE CASCADE.

Now let's say that a customer on the base should at least have a product:
when a product is removed, if it is the last product of a costumer then the costumer must be removed as well.

CREATE OR REPLACE TRIGGER RemoveCustomer
AFTER DELETE ON Product
BEGIN
        DELETE FROM Customer
        WHERE CustomerID IN (
                SELECT c.CustomerID
                FROM Customer c
                LEFT OUTER JOIN Product p
                    ON p.CustomerID = c.CustomerID
                GROUP BY c.CustomerID HAVING COUNT(p.CustomerID) = 0
        );
END;
/

This solution seems to be natural to me but Oracle dislikes it. At every DELETE of a product I get the error: 

ORA-00036: maximum number of recursive SQL levels (50) exceeded

This even if the DELETE wouldn't cause a program to be removed.

Surprisingly, this syntax works just fine:

CREATE OR REPLACE TRIGGER RemoveCustomer
AFTER DELETE ON Product
BEGIN
        FOR my_row IN (
                SELECT c.CustomerID
                FROM Customer c
                LEFT OUTER JOIN Product p
                    ON p.CustomerID = c.CustomerID
                GROUP BY c.CustomerID HAVING COUNT(p.CustomerID) = 0
        ) 
        LOOP
                DELETE FROM Customer WHERE CustomerID = my_row.CustomerID;
        END LOOP;

END;
/

Could somebody explain why is this happening?

EDIT:

Here there is a working example: 

CREATE TABLE Customer (
    CustomerID INTEGER                 PRIMARY KEY
);

CREATE TABLE Product (
    ProductID INTEGER                 PRIMARY KEY,
    CustomerID INTEGER,
    CONSTRAINT fk_Customer FOREIGN KEY (CustomerID)
                                        REFERENCES Customer
                                        ON DELETE CASCADE
);


INSERT INTo Customer (CustomerID) VALUES (0);
INSERT INTo Customer (CustomerID) VALUES (1);
INSERT INTo Customer (CustomerID) VALUES (2);
INSERT INTo Customer (CustomerID) VALUES (3);
INSERT INTo Customer (CustomerID) VALUES (4);
INSERT INTo Customer (CustomerID) VALUES (5);
INSERT INTo Customer (CustomerID) VALUES (6);

INSERT INTO Product (ProductID, CustomerID) VALUES (0, 0);
INSERT INTO Product (ProductID, CustomerID) VALUES (1, 0);
INSERT INTO Product (ProductID, CustomerID) VALUES (2, 1);
INSERT INTO Product (ProductID, CustomerID) VALUES (3, 2);
INSERT INTO Product (ProductID, CustomerID) VALUES (4, 3);
INSERT INTO Product (ProductID, CustomerID) VALUES (5, 3);
INSERT INTO Product (ProductID, CustomerID) VALUES (6, 3);
INSERT INTO Product (ProductID, CustomerID) VALUES (7, 4);
INSERT INTO Product (ProductID, CustomerID) VALUES (8, 5);
INSERT INTO Product (ProductID, CustomerID) VALUES (9, 5);
INSERT INTO Product (ProductID, CustomerID) VALUES (10, 6);


CREATE OR REPLACE TRIGGER RemoveCustomer
AFTER DELETE ON Product
BEGIN
        DELETE FROM Customer
        WHERE CustomerID IN (
                SELECT c.CustomerID
                FROM Customer c
                LEFT OUTER JOIN Product p
                    ON p.CustomerID = c.CustomerID
                GROUP BY c.CustomerID HAVING COUNT(p.CustomerID) = 0
        );
END;
/


/* This request will produce the error */
DELETE FROM Product WHERE CustomerID = 3;

Upvotes: 4

Views: 584

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132700

It is surprising, but it appears that a cascading delete statement on products always occurs after a delete is performed on customers - even if no customers are deleted. For example:

SQL> delete customer where customerid = 9999999;
delete customer where customerid = 9999999
       *
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "TTEST.REMOVECUSTOMER", line 2
...

With your second version of the trigger, the for loop body is never executed when there are no customers with no products, so the delete of customers never occurs and the infinite loop is avoided.

Upvotes: 2

Related Questions