CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
giggity
giggity

Reputation: 37

Trigger to delete row from another table

I have two tables which is FORUM and COMMENTS

FORUM PRIMARY KEY: FORUMID

COMMENTS FOREIGN KEY: FORUMID

and when the user would like to delete any row in forum, it must execute the trigger and delete the comments in that forum first

CREATE OR REPLACE trigger trg_delete_comments
BEFORE DELETE ON forum
FOR EACH ROW
DECLARE 
   v_forumID varchar(14);
   v_user    varchar(20);
BEGIN
   select forumID into v_forumID
   from forum
   where forumID =:OLD.forumID;

   DELETE COMMENTS
   WHERE FORUMID = v_forumID;
END;
/

When I try to execute this trigger, I get the error of "ORA-04091: table FORUM is mutating, trigger/function may not see it". Is there any way to solve it? Thank you

Upvotes: 2

Views: 2708

Answers (4)

GMB
GMB

Reputation: 222462

What you are describing is a functionality that can be easily provided through a foreign key constraint. You can configure a constraint with the on delete cascade clause so that when a parent record is deleted, children are deleted too.

Consider the following example:

create table forums (
    forum_id int primary key,
    user_id int
);

create table comments (
    comment_id int primary key,
    forum_id int,
    constraint fk_comments_forum 
        foreign key (forum_id) 
        references forums(forum_id)
        on delete cascade
);

select * from forums;

FORUM_ID | USER_ID
-------: | ------:
       1 |       1
       2 |       2

select * from comments;

COMMENT_ID | FORUM_ID
---------: | -------:
         1 |        1
         2 |        1
         3 |        2

delete from forums where forum_id = 1;
1 rows affected

select * from comments;

COMMENT_ID | FORUM_ID
---------: | -------:
         3 |        2

Upvotes: 2

Mureinik
Mureinik

Reputation: 311308

I think you may be reinventing the wheel here. You could simply have your foreign key defined as on delete cascade, and let the database do the heavy lifting for you:

ALTER TABLE comments
ADD CONSTRAINT fk_forum
FOREIGN KEY (forumid)
REFERENCES forum(forumid)
ON DELETE CASCADE

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I don't understand why you are using two queries for this. The logic appears to be:

BEGIN
   DELETE COMMENTS
   WHERE FORUMID = :OLD.forumID;
END;

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can convert this into AFTER STATEMENT trigger and then can delete the rows -

CREATE OR REPLACE trigger trg_delete_comments
AFTER DELETE ON forum
BEGIN

   DELETE COMMENTS
   WHERE FORUMID IN (select forumID
                     from forum
                     where forumID = :OLD.forumID);
END;
/

Upvotes: 0

Related Questions