CODEWITHSUNDEEP
mysqlsqldatabasetriggersmariadb
asinduvg
asinduvg

Reputation: 67

How to write trigger for after delete which contains another deletion for the same table in the trigger body?

Following is the relational schema,

college(ID int, name text, grade int);
friend(ID1 int, ID2 int);
likes(ID1 int, ID2 int);

I wrote a trigger that maintains the symmetry in friendship relationships. That means if (X, Y) is deleted from friend, (Y, X) should also be deleted.

Following are the table creations;

create table college
(
    id    int auto_increment primary key,
    name  text not null,
    grade int  not null
);

create table likes
(
    id1 int not null,
    id2 int
);

create table friend
(
    id1 int not null,
    id2 int not null,
    constraint friend_pk
        primary key (id1, id2),
    constraint friend_college_id_fk
        foreign key (id1) references college (id)
    constraint friend_college_id_fk2
        foreign key (id2) references college (id)
);

I wrote follwing trigger;

delimiter //
drop trigger if exists Friend_Delete //
create trigger Friend_Delete
    after delete
    on friend
    for each row
begin
    delete
    from friend
    where id1 = OLD.id2
      and id2 = OLD.id1;
end //
delimiter ;

But when I execute the following query,

delete from friend where id1 = 1 and id2 = 2;

It gives me this result; Can't update table 'friend' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

I googled this issue and it is referred as endless recursive calls are taken place. But When we say after delete, it is deleted and why we cannot perform another deletion here?

Upvotes: 1

Views: 705

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

The database engine is making sure that you cannot write a trigger that sets off a never ending sequence of triggers. It's not looking at the logic of your code, it's just making sure that you don't set off a series of trigger actions that keeps running. It doesn't really matter whether it's "after" the original delete.

In very general terms, I'd recommend handling this in application code, rather than database code. Triggers have a number of problems - they're hard to test, they're hard to debug, they need developers to remember that doing one thing (deleting a row in this case) has a side effect (automagically deleting another row), they can create weird performance issues (a cascading sequence of triggers firing might look like your database is slow).

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

delete from friend where id1 = 1 and id2 = 2;

With above query you are deleting rows where id1 =1 and id2= 2

Then the trigger is trying to delete all the rows with id1=2 and id2=1

which will also trigger Friend_Delete to delete rows with id1=1 and id2=2 and this is the first delete condition you used. So this way recursion is happening and an endless loop has been created.

Upvotes: 0

Related Questions