CODEWITHSUNDEEP
mysqltriggers
user3081458
user3081458

Reputation: 43

Adding a TRIGGER to phpMyAdmin MySQL table to DELETE a row in Table A it's corresponding row is deleted in Table B

I have two tables called RESERVATIONS and RESERVATION DETAILS.

The primary key reservationId for RESERVATIONS is a foreign key in RESERVATION DETAILS.

In my website, the user can delete a reservation from the RESERVATION table.

How can I add a TRIGGER so that, if the reservationId is deleted from the RESERVATION table, the row corresponding to the reservationId in RESERVATION DETAILS is also deleted?

Using phpMyadmin.

Upvotes: 2

Views: 1031

Answers (2)

Simon MᶜKenzie
Simon MᶜKenzie

Reputation: 8664

If you always want to unconditionally delete RESERVATION_DETAILS when you delete the parent row in RESERVATIONS, you can define ON DELETE CASCADE on your foreign key. Your definition for RESERVATION_DETAILS will look something like this:

CREATE TABLE RESERVATION_DETAILS (
    reservationDetailId INT,
    reservationId INT,
    -- etc...

    FOREIGN KEY (reservationId)
        REFERENCES RESERVATIONS(reservationId)
        ON DELETE CASCADE
);

See the MySQL documentation for further details.

Upvotes: 1

Ohgodwhy
Ohgodwhy

Reputation: 50787

You can make a simple trigger to delete from another table on delete:

CREATE TRIGGER delete_reservation_id AFTER DELETE on RESERVATIONS
    FOR EACH ROW
    BEGIN
        DELETE FROM RESERVATION_DETAILS
        WHERE RESERVATION_DETAILS.reservationId = RESERVATIONS.reservationId;
    END

Change your table names accordingly.

If you need to delete this trigger ever, this is the syntax:

DROP TRIGGER IF EXISTS delete_reservation_id ;

Upvotes: 0

Related Questions