父记录值变更时更新子记录（modified_date、is_delete）及多表问询

Alright, let's break down how to solve this problem. The main goal here is to automatically update the modified_date and is_delete fields in child records whenever the corresponding parent record's relevant values change. First, let's clearly outline the table structures we're working with:

Table Structures #

user Table #

country Table #

user_activity Table #

(Note: I’m filling in the implied fields you mentioned for clarity)

Solution: Use Database Triggers #

Triggers are perfect for this kind of automated, cascading update when parent records change. Below are practical examples for two widely used databases—MySQL and PostgreSQL.

1. Trigger for user Table Updates (Affecting user_activity) #

When a user record's status (or other critical fields) changes, we want to update all linked user_activity records. Here's how to set that up:

MySQL Example

-- Create trigger to sync user_activity when user status changes
DELIMITER //
CREATE TRIGGER update_user_activity_on_user_change
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
  -- Only run the update if the status field was actually modified
  IF OLD.status != NEW.status THEN
    UPDATE user_activity
    SET modified_date = NOW(),
        -- Adjust this CASE logic to match your business rules for marking records as deleted
        is_delete = CASE WHEN NEW.status = 'inactive' THEN 1 ELSE 0 END
    WHERE user_id = NEW.id;
  END IF;
END //
DELIMITER ;

PostgreSQL Example

-- First, create the trigger function
CREATE OR REPLACE FUNCTION update_user_activity()
RETURNS TRIGGER AS $$
BEGIN
  -- Check if the status field changed before proceeding
  IF OLD.status != NEW.status THEN
    UPDATE user_activity
    SET modified_date = CURRENT_TIMESTAMP,
        is_delete = CASE WHEN NEW.status = 'inactive' THEN TRUE ELSE FALSE END
    WHERE user_id = NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the function as a trigger to the user table
CREATE TRIGGER trigger_user_update
AFTER UPDATE ON user
FOR EACH ROW
EXECUTE FUNCTION update_user_activity();

2. Trigger for country Table Updates (Affecting user and user_activity) #

If a country record's status changes, you might need to first update linked user records, then let the existing user trigger handle cascading updates to user_activity. Here's that workflow:

MySQL Example

First, the trigger to update user records when a country changes:

DELIMITER //
CREATE TRIGGER update_users_on_country_change
AFTER UPDATE ON country
FOR EACH ROW
BEGIN
  IF OLD.status != NEW.status THEN
    UPDATE user
    SET status = NEW.status, -- Adjust this to match your business logic
        modified_date = NOW() -- Add this if your user table has a modified_date field
    WHERE country_id = NEW.id; -- Assuming user has a country_id foreign key linking to country
  END IF;
END //
DELIMITER ;

The user trigger we created earlier will automatically update user_activity records once the user records change.

PostgreSQL Example

-- Create the function to update users when country status changes
CREATE OR REPLACE FUNCTION update_users_from_country()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.status != NEW.status THEN
    UPDATE user
    SET status = NEW.status,
        modified_date = CURRENT_TIMESTAMP
    WHERE country_id = NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger to the country table
CREATE TRIGGER trigger_country_update
AFTER UPDATE ON country
FOR EACH ROW
EXECUTE FUNCTION update_users_from_country();

Again, the existing user trigger will take care of updating user_activity records automatically.

Quick Notes to Keep in Mind #

• Tweak the CASE statement for is_delete to align with your actual business rules (e.g., when exactly should a child record be marked as deleted?).
• Ensure foreign keys are properly configured between tables (user_activity.user_id references user.id, user.country_id references country.id) to keep data integrity intact.
• Always test these triggers in a staging environment first—you don’t want unintended data changes in production!

内容的提问来源于stack exchange，提问作者Saswat