基于表A allotment_status字段变更的表B SQL自动增删需求

Hey there! Let's tackle this automatic sync between Table A and Table B. The perfect tool for this job is database triggers—they run automatically when specific events (like an UPDATE) happen on a table. Below are step-by-step implementations for the most common relational databases:

MySQL Implementation #

We'll create two separate triggers: one for inserting into Table B when allotment_status switches to 1, and another for deleting when it switches to 0.

Trigger for Inserting into Table B #

DELIMITER //
CREATE TRIGGER trg_after_update_allotment_insert
AFTER UPDATE ON TableA
FOR EACH ROW
BEGIN
    -- Only run if status changed to 1
    IF NEW.allotment_status = 1 AND OLD.allotment_status != 1 THEN
        -- Replace 'GENERATED_ROOM_NO' with your actual room number logic
        INSERT INTO TableB (studentID, roomno)
        VALUES (NEW.studentID, 'GENERATED_ROOM_NO');
    END IF;
END //
DELIMITER ;

Trigger for Deleting from Table B #

DELIMITER //
CREATE TRIGGER trg_after_update_allotment_delete
AFTER UPDATE ON TableA
FOR EACH ROW
BEGIN
    -- Only run if status changed to 0
    IF NEW.allotment_status = 0 AND OLD.allotment_status != 0 THEN
        DELETE FROM TableB
        WHERE studentID = NEW.studentID;
    END IF;
END //
DELIMITER ;

PostgreSQL Implementation #

PostgreSQL uses a single function that can handle both insert and delete logic, which we'll attach to a trigger.

Trigger Function & Attachment #

CREATE OR REPLACE FUNCTION sync_allotment_status()
RETURNS TRIGGER AS $$
BEGIN
    -- Insert into Table B when status becomes 1
    IF NEW.allotment_status = 1 AND OLD.allotment_status != 1 THEN
        -- Adjust roomno logic here to match your workflow
        INSERT INTO TableB (studentID, roomno)
        VALUES (NEW.studentID, 'GENERATED_ROOM_NO');
    -- Delete from Table B when status becomes 0
    ELSIF NEW.allotment_status = 0 AND OLD.allotment_status != 0 THEN
        DELETE FROM TableB
        WHERE studentID = NEW.studentID;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the function to the trigger
CREATE TRIGGER trg_sync_allotment_status
AFTER UPDATE OF allotment_status ON TableA
FOR EACH ROW
EXECUTE FUNCTION sync_allotment_status();

SQL Server Implementation #

In SQL Server, we can handle both actions in a single trigger using the inserted and deleted system tables.

Combined Trigger for Insert & Delete #

CREATE TRIGGER trg_sync_allotment_status
ON TableA
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert into Table B where status switched to 1
    INSERT INTO TableB (studentID, roomno)
    SELECT i.studentID, 'GENERATED_ROOM_NO' -- Replace with your room number logic
    FROM inserted i
    JOIN deleted d ON i.studentID = d.studentID
    WHERE i.allotment_status = 1 AND d.allotment_status != 1;

    -- Delete from Table B where status switched to 0
    DELETE b
    FROM TableB b
    JOIN inserted i ON b.studentID = i.studentID
    JOIN deleted d ON i.studentID = d.studentID
    WHERE i.allotment_status = 0 AND d.allotment_status != 0;
END;
GO

Key Things to Keep in Mind #

• Room Number Logic: The 'GENERATED_ROOM_NO' placeholder needs to be replaced with your actual room assignment logic—this could be pulling from an available rooms table, using a sequence, or any other rule your system follows.
• Avoid Unnecessary Actions: All triggers check if the allotment_status actually changed (not just updated to the same value) to prevent redundant database operations.
• Permissions: Make sure the user creating these triggers has the required privileges (like CREATE TRIGGER in MySQL, TRIGGER access in PostgreSQL, or ALTER permissions on Table A in SQL Server).
• Test Thoroughly: Always test with sample data first—update a student's status to 1, verify Table B gets the new row; switch it back to 0, confirm the row is deleted.

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