MySQL 触发器 - 我哪里出错了

Question

有人可以告诉我在 MySQL 中创建这个触发器时哪里出了问题吗？每次我尝试创建它时，它都会不断告诉我有错误。

USE `cl11-onestock`;

DELIMITER //

CREATE TRIGGER audit_insert
AFTER INSERT 
    ON stock_usage FOR EACH ROW
BEGIN
    SET uUid = (SELECT UUID());
    SET stockUsageId = (SELECT NEW.stock_usage_id);
    SET siteId = (SELECT NEW.site_id);
    SET date = (SELECT NEW.date);
    SET patientName = (SELECT NEW.patient_name);
    SET dentistDiscountId = (SELECT NEW.dentist_discount_id);
    SET itemId = (SELECT NEW.item_id);
    SET quantity = (SELECT NEW.quantity);
    SET priceIncDiscount = (SELECT NEW.price_inc_discount);
    SET vat = (SELECT NEW.vat);
    SET priceIncVat = (SELECT NEW.price_inc_vat);
    SET createdAt = (SELECT NEW.created_at);
    SET updatedAt = (SELECT NEW.updated_at);

    INSERT INTO stock_audit VALUES
    (uUid, stockUsageId, siteId, date, patientName, dentistDiscountId, itemId, quantity, priceIncDiscount, vat, priceIncVat, createdAt, updatedAt);

END; //

DELIMITER ;

Answer 1

您需要使用关键字new插入后访问字段值，而不是像 select 那样您可以直接在插入查询中使用

所以触发器应该是这样的

DELIMITER //

CREATE TRIGGER audit_insert
AFTER INSERT 
    ON stock_usage FOR EACH ROW
BEGIN
       INSERT INTO stock_audit VALUES
    (new.UUID, NEW.stock_usage_id, NEW.site_id, NEW.date, NEW.patient_name, NEW.dentist_discount_id, NEW.item_id, NEW.quantity, NEW.price_inc_discount, NEW.vat, NEW.price_inc_vat, NEW.created_at, NEW.updated_at);

END; //

DELIMITER ;