CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Sebastian Corneliu Vîrlan
Sebastian Corneliu Vîrlan

Reputation: 1700

Database multiple tables trigger

I have a database with this structure:

How can I make a trigger when I update user_status = 'deleted' to automatically set:

question_status = deleted
answer_status = deleted
like_status = deleted

Upvotes: 0

Views: 56

Answers (3)

skbly7
skbly7

Reputation: 1162

What does this trigger do?

  • List item
  • Define new trigger
  • Check if user_status column is updated.
  • Check if user_status is updated to 'deleted'
  • Take id of that user, and change status of all of it's questions, answers, and likes to 'deleted'.

Code:

DELIMITER //
CREATE TRIGGER  user_delete BEFORE UPDATE ON users FOR EACH ROW
BEGIN
    IF NEW.user_status <> OLD.user_status
    THEN 
        IF STRCMP('deleted',NEW.user_status) = 0 THEN
            UPDATE question_status SET question_status = 'deleted' WHERE asker_id = NEW.id;
            UPDATE answers SET answer_status = 'deleted' WHERE who_answer_id = NEW.id;
            UPDATE likes SET like_status = 'deleted' WHERE liker_id = NEW.id;
        END IF;
        IF STRCMP('active',NEW.user_status) = 0 THEN
            UPDATE question_status SET question_status = 'active' WHERE asker_id = NEW.id;
            UPDATE answers SET answer_status = 'active' WHERE who_answer_id = NEW.id;
            UPDATE likes SET like_status = 'active' WHERE liker_id = NEW.id;
        END IF;
    END IF;
END//
DELIMITER ;

Possible flow you might notice later

Just consider this scenario on your site, QuestionA was already 'deleted' by some moderator, now when user (which is poster of that question) is re-activated, all such moderated questions/answers would come back !

You should ideally have another status to decide that it was automatically deleted or manually. Maybe 'auto_delete' and 'delete' instead of just using 'delete'.

Upvotes: 1

Sebastian Corneliu V&#238;rlan
Sebastian Corneliu V&#238;rlan

Reputation: 1700

BEGIN
    IF NEW.user_status <> OLD.user_status
    THEN 
        IF STRCMP('deleted',NEW.user_status) = 0 THEN
            UPDATE questions SET question_status = 'deleted' WHERE asker_id = NEW.id AND question_status != 'mod_deleted';
            UPDATE answers SET answer_status = 'deleted' WHERE commentator_id = NEW.id AND answer_status != 'mod_deleted';
            UPDATE likes SET like_status = 'deleted' WHERE liker_id = NEW.id AND like_status != 'mod_deleted';
        END IF;
        IF STRCMP('active',NEW.user_status) = 0 THEN
            UPDATE questions SET question_status = 'active' WHERE asker_id = NEW.id AND question_status != 'mod_deleted';
            UPDATE answers SET answer_status = 'active' WHERE commentator_id = NEW.id AND answer_status != 'mod_deleted';
            UPDATE likes SET like_status = 'active' WHERE liker_id = NEW.id AND like_status != 'mod_deleted';
        END IF;
    END IF;
END

_status fields are enum type so if I add mod_deleted and in the update I add a AND should solve the flow

Upvotes: 1

Zax
Zax

Reputation: 698

Assuming you are using SQL server (because I see no database specified) I would say do 1 trigger on table users as follows (may not be exact)

CREATE TRIGGER [user_statusDeleted]
ON [users]
After UPDATE, INSERT --INSERT may not be relevant
AS 
BEGIN
UPDATE q
SET q.[question_status] = deleted
FROM [inserted] i 
    JOIN [questions] q
        ON i.[Id] = q.[asker_id] --or asked_id ?
        AND i.[user_status] = deleted

Then add two more updates for tables answers and likes after this one (sorry, I would be guessing the joins anyway), separated by semicolon and it should work.

more on triggers in sql server here - https://msdn.microsoft.com/en-us/library/ms189799.aspx

Upvotes: 1

Related Questions