My SQL trigger that set foreign key to null-CodePudding

I have 2 tables in mysql db, faq_category and faq. faq_category_id is foreign key.

faq
------------------------------
faq_id | faq_category_id | question

faq_category
------------------------------
faq_category_id | name | deleted_at (timestamp)

I use soft delete in the PHP, so I want when faq category is deleted, field faq_category_id in faq table to be set to null.

I have the following code:

DELIMITER //
    CREATE TRIGGER _set_faq_foreign_key_to_null
    AFTER UPDATE ON faq_category 
    FOR EACH ROW
    BEGIN
        IF !(NEW.deleted_at <=> OLD.deleted_at) THEN
        update faq 

        set faq.faq_category_id = NULL
       WHERE faq.faq_category_id = faq_category_id;
   END IF;
   END; //

This code set ALL fields faq_category_id in the faq table to null.

I want to update faq table but only the rows where faq_category_id is equal to faq_category_id which has been marked as deleted to be set to null, not all rows.

Any help will be deeply appreciated

CodePudding user response：

To the WHERE clause use a reference to the NEW.faq_category_id:

WHERE faq.faq_category_id = NEW.faq_category_id;