删除时级联不激活触发器

Question

我有以下三个表：

• materials (id, name, ...)
• stock_activities (id, material_id, warehouse_id, ..., detail_id)
• stock_activity_details (id, title, ...)

stock_activities.material_id上有一个外键常量，因此如果删除了material，则与已删除行相对应的行也将从stock_activities中删除。到目前为止，一切都很好。

我正在分组一些"；股票活动"；通过使用公共详细信息，并且因为我不想在每个活动行上重复这些详细信息，所以我将详细信息存储在另一个表上，并引用活动行上的详细信息id。这会出现删除问题。当删除一个活动行时，我想删除多个活动行共用的详细信息行。

我考虑过使用触发器(我可能是第一次添加(，并创建了以下内容：

CREATE TRIGGER delete_activity_detail
AFTER DELETE ON stock_activities FOR EACH ROW
DELETE FROM stock_activity_details
WHERE stock_activity_details.id = OLD.detail_id

如果我从stock_activities表中删除一行，这似乎有效，但如果我删除一个材料，这似乎不起作用。当我删除一个材料时，外键constint会从stock_activities中删除一行，但stock_activities上的删除似乎不会激活触发器(这将删除活动详细信息(。

这是预期的行为吗？如果是，有没有我可以使用的变通方法？

如果我不能在数据库中做到这一点，我将不得不在代码中手动完成，但我认为最好在数据库中处理。

---

测试用例的SQL：

CREATE TABLE `materials` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `stock_activities` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`material_id` int UNSIGNED NOT NULL,
`warehouse_id` int UNSIGNED NOT NULL,
`detail_id` int UNSIGNED NOT NULL,
CONSTRAINT `stock_activities_ibfk_1`
FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `stock_activity_details` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `materials` (`id`, `name`) VALUES
(1, 'Foo'),
(2, 'Bar');
INSERT INTO `stock_activity_details` (`id`, `title`) VALUES
(1, 'Lorem'),
(2, 'ipsum');
INSERT INTO `stock_activities` (`material_id`, `detail_id`, `warehouse_id`) VALUES
(1, 1, 8),
(2, 2, 9);
CREATE TRIGGER delete_activity_detail
AFTER DELETE ON stock_activities FOR EACH ROW
DELETE FROM stock_activity_details
WHERE stock_activity_details.id = OLD.detail_id;

Answer 1

MariaDB：：触发限制

• 外键操作不会激活触发器

。。。

几个选项可以给你一些想法：

CREATE TABLE `materials` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `stock_activity_details` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `stock_activities` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`material_id` int UNSIGNED NOT NULL,
`warehouse_id` int UNSIGNED NOT NULL,
`detail_id` int UNSIGNED NOT NULL,
CONSTRAINT `stock_activities_ibfk_1`
FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`),
CONSTRAINT `stock_activities_ibfk_2`
FOREIGN KEY (`detail_id`) REFERENCES `stock_activity_details` (`id`)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

选项1:

CREATE TRIGGER `delete_stock_activities`
BEFORE DELETE ON `materials`
FOR EACH ROW
DELETE FROM
`stock_activities`
WHERE
`stock_activities`.`material_id` = OLD.`id`;
CREATE TRIGGER `delete_stock_activity_details`
AFTER DELETE ON `stock_activities`
FOR EACH ROW
DELETE FROM
`stock_activity_details`
WHERE
`stock_activity_details`.`id` = OLD.`detail_id`;

请参见dbfiddle。

选项2:

CREATE TRIGGER `delete_stock_activity_details`
BEFORE DELETE ON `materials`
FOR EACH ROW
DELETE
`stock_activities`, `stock_activity_details`
FROM
`stock_activity_details`
INNER JOIN `stock_activities` ON
`stock_activities`.`material_id` = OLD.`id`
WHERE
`stock_activity_details`.`id` = `stock_activities`.`detail_id`;

请参见dbfiddle。