Trigger to delete from a second MySQL table

Question

I have written tables in a MySQL database. There happens to be an Art table, and three related tables, Sculpture, Painting, and Photo.

Every item in Art also happens to be in one and only one of the other three. I am trying to write a trigger so that when I delete a tuple in Painting, the trigger will delete the tuple in Art which corresponds.

This should only work for Painting, and not affect either Sculpture or Photo. Every one of these items has an id_no, and there is a matching id_no in Painting for each one that is a painting. I can't just delete any id_no in Art that's not in Painting, since it may still exist in Sculpture or Photo.

So I'm not sure if I should go about this by temporarily saving the id_no somewhere (that was deleted from Painting), then doing a comparison and deleting in the trigger (from Art), or even how that would be done.

DELIMITER $ Create trigger delete_painting After delete on Painting Delete from Art WHERE

ridecar2 · Accepted Answer · 2012-10-29 01:59:04Z

1

DELIMITER $
CREATE TRIGGER delete_painting AFTER DELETE ON Painting
    DELETE FROM Art WHERE OLD.id_no = Art.id_no; $

The 'table' OLD contains only the row you have deleted so you can do this.

answered Oct 29, 2012 at 1:59

ridecar2

1,95716 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger to delete from a second MySQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related