0

I have a products table, a category table and a join-table to link the two, named prod_cat_join. I am attempting to periodically delete all categories not associated with a product with a specific team ID. Let it be known that category has a trigger that deletes all joins in prod_cat_join associated with a category after a category is deleted. Is there a single SQL query that can be invoked to delete all categories not associated with a product and is associated with a specific team ID without running into a function/trigger error?

The trigger goes as follows: 

CREATE DEFINER=`dbe1`@`%` TRIGGER `db`.`category_AFTER_DELETE` AFTER DELETE ON `category` FOR EACH ROW
BEGIN
delete from prod_cat_join  where prod_cat_join.categoryID = ID;
END

And my query that is periodically called to delete all categories not associated with a product is:

DELETE db.category FROM db.category 
LEFT JOIN db.prod_cat_join AS join_cat 
ON join_cat.categoryID = db.category.ID 
WHERE join_cat.productID IS NULL AND db.category.teamID = '1234'

I know it's possible to query all categories not associated with a product then delete the returned set of unassociated categories. But that seems inefficient as it requires two queries. Given our current setup is it possible to bypass the trigger to run the aforementioned query? Or is there some other single query method to delete the unassociated categories? And would putting an after delete trigger in prod_cat_join to delete all categories who no longer have an association to a join be effective? Would that not end up invoking the above-mentioned deletion trigger?

Guidance would be appreciated, thank you.

HDubz
  • 167
  • 1
  • 2
  • 14
  • Is there a reason you're using a trigger instead of a foreign key with `ON DELETE CASCADE`? – Barmar May 10 '19 at 20:13
  • See https://stackoverflow.com/questions/13598155/how-to-disable-triggers-in-mysql for how to temporarily disable the trigger using a global variable. – Barmar May 10 '19 at 20:19
  • @Barmar As I've understand the reverse order of the adjacency is required. – Kondybas May 10 '19 at 20:21
  • I don't think so. `prod_cat_join` has a foreign key to `category`. When you delete from `category`, it deletes all the corresponding rows from `prod_cat_join`. That's what `ON DELETE CASCADE` does. – Barmar May 10 '19 at 20:23
  • @Barmar But @HDubz want to delete `category` entries having no references in the `join_cat` table. I think that another trigger for `join_cat` should delete the `category` with no `products` – Kondybas May 10 '19 at 20:27
  • Deleting all categories that have no associated join is probably not a good idea. It might just be a temporary condition. – Barmar May 10 '19 at 20:27
  • @Kondybas I'm not suggesting that he should delete from `category` with `ON DELETE CASCADE`. I'm saying that the trigger that deletes from `prod_join_cat` should be done with `ON DELETE CASCADE`. – Barmar May 10 '19 at 20:29

0 Answers0