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.