跨数据库表触发更新实现：当表字段变更时更新另一库表值

API 探路者

2026-5-12

Cross-Database Trigger to Sync Updated Values

Hey there! Let's fix up that trigger you're working on to sync values between your two databases. First, let's go over the issues in your original code, then walk through the correct implementation.

Key Issues in Your Original Code

You can't target a specific column directly in the AFTER UPDATE ON clause (like table1.value). Triggers are bound to the entire table, so we'll add a check inside the trigger to only run the update when the value column changes.
Your syntax is missing proper delimiters (critical for multi-statement triggers in MySQL) and a closing statement for the trigger block.
Make sure the user creating the trigger has UPDATE permissions on database2.useragetable and TRIGGER permissions on database1.table1.

Correct Trigger Implementation (MySQL Example)

Assuming both databases are on the same server instance, here's the working code:

-- Change delimiter to avoid conflicts with semicolons inside the trigger
DELIMITER //

CREATE TRIGGER sync_user_age
AFTER UPDATE ON database1.table1
FOR EACH ROW
BEGIN
    -- Only run the sync if the `value` column was actually modified
    IF NEW.value <> OLD.value THEN
        UPDATE database2.useragetable
        SET age = NEW.value
        WHERE no = NEW.no;
    END IF;
END //

-- Reset back to default delimiter
DELIMITER ;

Important Notes

Same Instance Requirement: This only works if both databases are hosted on the same database server instance. If they're on separate servers, you'll need an alternative approach (like a message queue, ETL tool, or a custom script that listens for changes via binlogs).
Test Thoroughly: After creating the trigger, manually update a value in database1.table1 and verify that the corresponding age in database2.useragetable updates correctly.
Error Handling: If you need to handle cases where the cross-database update fails, you can add an exception handler inside the trigger block, like:
```
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- Add your error logging/handling logic here
END;
```