MariaDB触发器语法错误排查：Build表插入后判断硬件兼容性

Let's fix that trigger step by step—your 1064 error comes from a few syntax mistakes and incorrect logic in how you're checking compatibility. Here's what went wrong and the corrected code:

Key Issues in Your Original Code #

1. Missing Delimiter Change: MariaDB/MySQL uses ; as the default statement delimiter. When creating a trigger with multiple statements (like IF/ELSE blocks), you need to temporarily change the delimiter first—otherwise the database will interpret the first ; as the end of the trigger definition, causing a syntax error.
2. Incorrect EXISTS Syntax: Your subquery structure for checking socket compatibility is invalid. You also don't need to query the Build table again to get the new record—triggers let you access the newly inserted row directly with the NEW keyword.
3. Field Name Typo: You tried inserting into compatibility(compatibility), but your table's field is named compatible.
4. Unnecessary Query: Fetching the latest Build row with ORDER BY ID DESC LIMIT 1 is risky (concurrency issues) and unnecessary when you can use NEW to get the exact row that triggered the event.

Corrected Trigger Code #

DELIMITER //

CREATE TRIGGER check_build_compatibility
AFTER INSERT ON Build
FOR EACH ROW
BEGIN
    DECLARE proc_socket VARCHAR(50);
    DECLARE mobo_socket VARCHAR(50);

    -- Get the socket of the newly inserted processor
    SELECT Socket INTO proc_socket
    FROM Processor_products
    WHERE Name = NEW.Processor;

    -- Get the socket of the newly inserted motherboard
    SELECT Socket INTO mobo_socket
    FROM Motherboard
    WHERE Name = NEW.Motherboard;

    -- Compare sockets and insert result into Compatibility
    IF proc_socket <> mobo_socket THEN
        INSERT INTO Compatibility(compatible) VALUES ('No');
    ELSE
        INSERT INTO Compatibility(compatible) VALUES ('Yes');
    END IF;
END //

DELIMITER ;

How This Works #

• DELIMITER //: Changes the statement delimiter to // so the database doesn't stop parsing the trigger at the first ;. We reset it back to ; after the trigger is created.
• FOR EACH ROW: Ensures the trigger runs once for every row inserted into Build.
• NEW.Processor/NEW.Motherboard: Directly references the Processor and Motherboard values from the row that was just inserted.
• Declared Variables: We fetch the socket values into variables first for clarity, then compare them to decide whether to insert "Yes" or "No" into the Compatibility table.

How to Run This in phpMyAdmin #

1. Go to the SQL tab in your database.
2. Paste the entire code block above (including the DELIMITER lines).
3. Click "Go" to execute the query.

This should resolve the 1064 syntax error and correctly handle the compatibility check for every new Build entry.

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