如何創建MySQL觸發器

您好，我正在嘗試為這樣的表撰寫觸發器：

CREATE TABLE `sales_payment_method` (
  `id` int(11) NOT NULL,
  `payment` enum('CASH','CARD','CHECK','TRANSFERENCE','CREDIT') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  KEY `id` (`id`),
  CONSTRAINT `sales_payment_method_ibfk_1` FOREIGN KEY (`id`) REFERENCES `sales_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

您可以使用不同的付款方式組合，例如，如果您必須支付 100 美元進行銷售，id=15并且假設您沒有足夠的現金，那么您可以使用信用卡和現金付款。這樣sales_payment_method我們就可以添加這兩個新行：

INSERT INTO sales_payment_method VALUES (15, 'CASH', 70.00) 和 INSERT INTO sales_payment_method VALUES (15, 'CARD', 30.00)

好吧，雖然我在 PHP 中進行了一些驗證，但我想使用觸發器，這樣我就可以防止感覺更安全。

除了 CREDIT 這意味著客戶擁有 CREDIT 并且可以在不付款的情況下拿走產品，這些驗證在其他地方進行，但在此表中這三件事必須完成

• 如果銷售付款方式不是 CREDIT，您可以有一種或多種付款方式，但沒有 CREDIT
• 我們不能多次將相同的付款方式與同一個銷售 ID 相關聯
• 第 3 個有點多余，但如果一種銷售付款方式是 CREDIT，我們不得允許另一種付款方式

我試圖通過創建以下觸發器來完成前面的要點，但有一個錯誤

BEGIN
    DECLARE i INT;
    DECLARE paymentMethod VARCHAR(15);
    SELECT COUNT(*)
    INTO i
    FROM sales_payment_method
    WHERE id=NEW.id;
    
    SET paymentMethod = (SELECT payment
    FROM sales_payment_method WHERE 
    id=NEW.id);
    
    IF i= 1 AND NEW.payment = "CREDIT" THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You can not use CREDIT when there's already a different payment method";
    END IF;
    IF i= 1 AND paymentMethod LIKE NEW.payment THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "This payment method is already associated to this ID";
    END IF;
    IF i= 1 AND paymentMethod LIKE "CREDIT" THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "This sale payment method is CREDIT, you can not use a different payment method";
    END IF;
END

當我嘗試插入一些東西來測驗觸發器時，第一個 IF 似乎作業正常，但我收到一個 MySQL 1267 錯誤，'非法混合排序'我認為那是因為 paymentMethod 是 VARCHAR，而 payment 是一個列舉，但我沒有知道如何解決。

我希望你能指導我朝著正確的方向前進。

謝謝！

uj5u.com熱心網友回復：

如果默認字符集與您在 tabem 中使用的不同，您必須定義所有變數，然后使用您使用的字符集和排序規則或至少一個類似的

CREATE TABLE `sales_payment_method` (
  `id` int(11) NOT NULL,
  `payment` enum('CASH','CARD','CHECK','TRANSFERENCE','CREDIT') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  KEY `id` (`id`)
  #,
  #CONSTRAINT `sales_payment_method_ibfk_1` FOREIGN KEY (`id`) REFERENCES `sales_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TRIGGER ins_check BEFORE INSERT ON sales_payment_method
       FOR EACH ROW 
BEGIN
    DECLARE i INT;
    DECLARE paymentMethod VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    SELECT COUNT(*)
    INTO i
    FROM sales_payment_method
    WHERE id=NEW.id;
    
    SET paymentMethod = (SELECT payment
    FROM sales_payment_method WHERE 
    id=NEW.id);
    
    IF i= 1 AND NEW.payment = "CREDIT" THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You can not use CREDIT when there's already a different payment method";
    END IF;
    IF i= 1 AND paymentMethod <> NEW.payment THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "This payment method is already associated to this ID";
    END IF;
    IF i= 1 AND paymentMethod LIKE "CREDIT" THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "This sale payment method is CREDIT, you can not use a different payment method";
    END IF;
END

INSERT INTO sales_payment_method VALUES (15, 'CASH', 70.00) 

INSERT INTO sales_payment_method VALUES (15, 'CARD', 30.00)

此付款方式已與此 ID 關聯

db<>在這里擺弄

標籤：mysql 触发器

上一篇：如何將mysql列中的數字添加到列并保存資料庫？

下一篇：是否可以先對一系列值進行排序，然后在MySQL中對其余值進行排序？[復制]