是否可以根据TYPE值在CASCADE和SET NULL之间切换

Question

类别表：

ID	NAME
5
18
7

据我所知，从约束的角度来看，没有直接的方法可以在CASCADE和SET NULL之间动态更改。虽然我不确定这是否是最好的方法，但我们当然可以使用触发器来动态执行规则。在创建触发器之前，请确保将FK.的ON DELETE设置为SET NULL

delimiter //
DROP TRIGGER IF EXISTS  before_delete//
create trigger before_delete before delete on CATEGORIES for each row
begin
if exists (select 1 from SUBCATEGORIES where CATEGORY_ID=old.ID and type=45) then
delete from SUBCATEGORIES where CATEGORY_ID=old.ID and type=45;
end if;
end//
delimiter ;
delete from CATEGORIES where ID=5;
delete from CATEGORIES where ID=7;
select * from SUBCATEGORIES;
-- result set (the NAME column removed)
# ID, CATEGORY_ID, TYPE
5, , 63
6, 18, 63
13, 18, 45
15, 18, 63
21, , 63
26, 18, 45