Oracle: ON DELETE CASCADE causing trigger recursion

Question

Take two tables as an example: Customer containing customers and Product containing products purchased / used by customers.

Each product refers to a customer through the CustomerID foreign key , which corresponds to the primary key of the Customer table (they also have the same name).

When a customer is deleted, all products that reference this customer are deleted: Product.CustomerID has the ON attribute . DELETE CASCADE .

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

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 natural to me, but Oracle doesn't like it. Every time I uninstall the product, I get an error:

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

This is even if DELETE does not delete the program.

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

Can someone explain why this is happening?

EDIT:

There is a working example here:

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;

Answer 1

Surprisingly, it seems that the cascading delete expression does not products always occur after the delete in customers, even if the clients are not deleted. For instance:

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

for , - , customers , .