数据库触发器设置：特定表仅允许单个属性修改是否可行？

Great question—restricting updates to only a single column (while locking others after initial insert) is a common use case, and triggers are exactly the right tool for the job. The core idea is to create a BEFORE UPDATE trigger that checks if any of the "locked" columns have been modified, and throws an error if they have.

Let me walk you through examples for two popular databases to make this concrete:

Example 1: MySQL/MariaDB #

Suppose your table is named customer_data, and you only want to allow updates to the last_login column—all other columns (like name, email, signup_date) should be immutable after insertion.

Here's the trigger code:

DELIMITER //
CREATE TRIGGER restrict_customer_updates
BEFORE UPDATE ON customer_data
FOR EACH ROW
BEGIN
    -- Check if any locked columns have changed
    IF OLD.name != NEW.name OR OLD.email != NEW.email OR OLD.signup_date != NEW.signup_date THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Updates are only allowed to the last_login column';
    END IF;
END //
DELIMITER ;

• The BEFORE UPDATE trigger runs before the change is applied, so it can block invalid updates immediately.
• OLD refers to the row's state before the update, NEW is the proposed new state. We compare each locked column to ensure they haven't changed.
• SIGNAL SQLSTATE '45000' throws a custom error that will abort the update.

Example 2: PostgreSQL #

For PostgreSQL, the logic is similar, but the syntax for raising errors is a bit different. Using the same customer_data table example:

CREATE OR REPLACE FUNCTION restrict_customer_updates()
RETURNS TRIGGER AS $$
BEGIN
    -- Check locked columns
    IF OLD.name <> NEW.name OR OLD.email <> NEW.email OR OLD.signup_date <> NEW.signup_date THEN
        RAISE EXCEPTION 'Updates are only allowed to the last_login column';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER restrict_customer_updates_trigger
BEFORE UPDATE ON customer_data
FOR EACH ROW EXECUTE FUNCTION restrict_customer_updates();

• PostgreSQL uses trigger functions that return the modified row (or null to abort), so we return NEW only if the check passes.
• RAISE EXCEPTION will halt the update and return your custom message to the caller.

Key Notes #

• Lock all non-allowed columns: Make sure you include every column that shouldn't be modified in your condition. If you miss one, users will be able to update it accidentally.
• Handle NULL values: If your columns can be NULL, use IS NOT DISTINCT FROM instead of !=/<> (since NULL != NULL evaluates to NULL, not true). For example: OLD.name IS NOT DISTINCT FROM NEW.name would correctly handle NULLs.
• Test thoroughly: Try updating the allowed column (it should work) and try updating a locked column (it should throw an error immediately).

This approach works across most relational databases—you just need to adjust the syntax for raising errors and defining triggers to match your specific system.

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