0

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!

closed as off-topic by Justin Cave, Michael Green, Philᵀᴹ, mustaccio, Jack Douglas Jul 16 '16 at 17:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Justin Cave, Michael Green, Philᵀᴹ, mustaccio, Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

  • You have something else going on. That trigger would not lead to a mutating table error on its own. Potentially you have some other trigger that isn't shown that is getting fired and causing the error (this is one of many reasons that putting logic in triggers is a dangerous game to play-- it makes it very difficult to walk through a piece of code and know everything that it is going to do). – Justin Cave Jul 15 '16 at 0:28
  • @ Justin Cave I deleted all the tables and created then anew. and this time I am getting a Referential integrity constraint violation. I know this refers to values in the child table, but I still cannot figure out why it acts like that. – omar Jul 15 '16 at 1:53
  • Do you have any other tables? Edit your question to contain a complete test case that reproduces the error and the full error stack. – Justin Cave Jul 15 '16 at 1:56
  • @ Justin Cave I want to thank you Justin. Your last comment gave me the hint I needed. I do indeed have another table referencing the Store table, so I included it in the trigger. : I added Delete from department where department.storeCode= :oldRow.stCode; And it worked! – omar Jul 15 '16 at 2:09
  • 1
    After working on many systems where developers have stuck various bits of code in triggers I will say that you shouldn't use a trigger unless you have really thought about it and decided it can't be done as a call to a procedure. Debugging can be a pain (as you've seen), code is hidden, stuff happens you don't expect and you can get performance issues as multiple triggers may fire for an apparently simple operation. – BriteSponge Jul 15 '16 at 8:27
0

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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.