How to soft delete using an AFTER DELETE Trigger in mysql that does not cascade delete the child table, but updates it in mysql

Question

So my parent table, which is a lookup table shares an attribute with my child table (foreign key). So my goal is to have an after_delete trigger, or any trigger that will update the foreign key in the child table when a row in a parent table is deleted, without having to delete the whole row containing information in the child table. For example, I have a table named Status with the the attributes StatusID and Status. The statuses are WORKING and BROKEN. Then I have a Laptops Table that has the attributes LaptopID, LaptopName, and StatusID (Foreign Key). For my Status Table, I have:

| 1 | WORKING | 

| 2 | BROKEN  | 

Now Let's say I have a record:

1001 | Lenovo 17XS | 1 | 

1002 | DELL XPS    | 2 |

My goal is when I delete the second row of my status table, I want the second row of my Laptops table to say:

1002 | DELL XPS   | DELETED |

I have turned cascade delete off for the foreign key so it does not delete the whole record.

This is my code for my attempt:

CREATE DEFINER=`TEST`@`%` TRIGGER `status_AFTER_DELETE` AFTER DELETE ON `status` FOR EACH ROW BEGIN
UPDATE Laptops INNER JOIN Status ON Laptops.StatusID = Status.StatusID
Set Laptops.StatusID = 0 WHERE Status.StatusID = Laptops.StatusID;
END

However, the trigger is not working. I have added a record called "DELETED" with a StatusID 0 afterwards but the trigger is still not working. What am I doing wrong?

Answer 1

An after update trigger is too late, you could do this in a before trigger and you should reference OLD. values (see manual for details) nb debugging triggers is a pain and I usually write to a debug_table when I get into difficulty. eg

DROP TABLE IF EXISTS laptops;
drop table if exists status;
drop trigger if exists t;

create table status(statusid int primary key, val varchar(20));

create table laptops( LaptopID int, LaptopName varchar(20), StatusID int,
foreign key fk1(statusid) references status(statusid)) ;

insert into status values
(1,'working'),(2,'broken'),(0,'deleted');

insert into laptops values
(1001 , 'Lenovo 17XS', 1 ),
(1002 ,  'DELL XPS'  , 2 );

delimiter $$
CREATE TRIGGER `status_AFTER_DELETE` before DELETE ON `status` 
FOR EACH ROW 
BEGIN
insert into debug_table(msg) values ('fired');
UPDATE Laptops 
 Set Laptops.StatusID = 0 WHERE old.StatusID = Laptops.StatusID;
END $$
delimiter ;
truncate table debug_table;
delete from status where statusid = 2;
select * from status;
select * from debug_table;