如何用纯SQL实现：更新ENUM类型rank列时自动赋值permissions列且可单独修改权限

Absolutely! You can pull this off with pure SQL using database triggers—this approach lets you auto-set permissions when the rank is updated, while still letting users manually tweak permissions without altering the rank. Below’s a concrete implementation using MySQL (since you mentioned the ENUM type, which is native to MySQL), plus notes for other databases.

Step 1: Create the User Permissions Table #

First, define your table with the ENUM rank column and a permissions column (we’ll use JSON here for flexible permission storage, but you could also use a TEXT column if preferred):

CREATE TABLE user_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rank ENUM('guest', 'user', 'admin') NOT NULL,
    permissions JSON NOT NULL
);

Step 2: Add Triggers to Auto-Set Permissions #

We’ll create two triggers: one for INSERT (to set permissions when a new row is added) and one for UPDATE (to only update permissions if the rank was modified). This ensures manual changes to permissions aren’t overwritten unless the rank changes.

Trigger for INSERT Operations #

DELIMITER //
CREATE TRIGGER set_permissions_on_insert
BEFORE INSERT ON user_permissions
FOR EACH ROW
BEGIN
    CASE NEW.rank
        WHEN 'guest' THEN
            SET NEW.permissions = '["read"]';
        WHEN 'user' THEN
            SET NEW.permissions = '["read", "update"]';
        WHEN 'admin' THEN
            SET NEW.permissions = '["read", "create", "update", "delete"]';
    END CASE;
END //
DELIMITER ;

Trigger for UPDATE Operations #

This trigger only runs if the rank value has changed (so it won’t overwrite manual permission edits):

DELIMITER //
CREATE TRIGGER set_permissions_on_rank_update
BEFORE UPDATE ON user_permissions
FOR EACH ROW
BEGIN
    IF OLD.rank != NEW.rank THEN
        CASE NEW.rank
            WHEN 'guest' THEN
                SET NEW.permissions = '["read"]';
            WHEN 'user' THEN
                SET NEW.permissions = '["read", "update"]';
            WHEN 'admin' THEN
                SET NEW.permissions = '["read", "create", "update", "delete"]';
        END CASE;
    END IF;
END //
DELIMITER ;

How It Works #

• When inserting a new user: The INSERT trigger automatically assigns the predefined permissions based on the rank you specify. For example:

  INSERT INTO user_permissions (rank) VALUES ('admin');
  -- permissions will automatically be set to ["read", "create", "update", "delete"]
  

• When updating the rank: If you change a user’s rank, the UPDATE trigger replaces their permissions with the new rank’s predefined set.
• When modifying permissions manually: If you only update the permissions column (without touching rank), the triggers won’t run—your custom permissions stay intact:

  UPDATE user_permissions 
  SET permissions = '["read", "update", "share"]' 
  WHERE rank = 'user';
  -- rank remains 'user', permissions are now your custom set
  

Notes for Other Databases #

If you’re using PostgreSQL (which doesn’t have a native ENUM type but supports custom types or CHECK constraints) or SQL Server, the core logic stays the same—you’ll just adjust the trigger syntax slightly:

• For PostgreSQL: Use CREATE FUNCTION to define the permission-setting logic, then attach it to BEFORE INSERT/BEFORE UPDATE triggers.
• For SQL Server: Use CREATE TRIGGER with IF UPDATE(rank) to check if the rank was modified.

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