Trigger to delete with cascade effects [closed]

Question

Hi I am new to Oracle triggers. Your help will be much appreciated! I have the following 2 tables: Product:

CREATE TABLE Product
  (prodId varchar(10) NOT NULL PRIMARY KEY,
   prodName varchar(30) NOT NULL,
  storeCode varchar(10) NOT NULL,
  CONSTRAINT prodStore FOREIGN KEY (storeCode) REFERENCES Store(stCode)
  );

and the Store table:

CREATE TABLE Store 
(stCode varchar(10) NOT NULL PRIMARY KEY,
storeName varchar(20) NOT NULL
);

I want to create an Oracle delete trigger so that if I delete a store, all the products that are related to it will be deleted as well, in a cascade. note: I don't want to use an ON DELETE CASCADE CONSTRAINT. Here's my code:

CREATE OR REPLACE TRIGGER trigg_suppression
BEFORE DELETE ON Store
FOR EACH ROW
BEGIN
DELETE FROM Product
WHERE Product.storeCode= :OLD.stCode;
END;
/

I get a mutating table error.

I tried to use a statement level trigger:

CREATE OR REPLACE TRIGGER trigg_suppression
BEFORE DELETE ON Store
BEGIN
DELETE FROM Product
WHERE Product.storeCode= Store.stCode;
END;
/

This won't even compile.

Thank you!

Answer 1

Thanks to @ Justin Cave I got the answer. The problem was that another table (Department) was referencing the Store table as well. so I added in the trigger body:

DELETE FROM Department
WHERE Department.storeCode = :oldRow.stCode;