How to creatae MySQL trigger-CodePudding

Hello I'm trying to write a trigger for a table table thats something like this:

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

You can have a different combination of payment methods, for example, if you have to pay $100 for sale id=15 and let's say you don't have cash enough, well you can use you're card and your cash to pay. So that in sales_payment_method we could add these two new rows:

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

Well although I make some validations in PHP I want to use use triggers so I can prevent feel more secure.

All payment methods are treated similarly in these tagble except for CREDIT which means the client has CREDIT and can take the products without paying, those validations are made somewhere else but in this table these 3 things must be accomplished

If a sale payment method is other than CREDIT, you can have one or more payment methods but no CREDIT
We must not have the same payment method associated to the same sale id more than once
3rd one a bit redundant but if one sale payment method is CREDIT, we must not permit another payment method

I tried to accomplish the previous points by creating the following trigger but there's a mistake

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

When I tried to insert something to test the trigger the first IF seems to work fine, but I get an MySQL 1267 error, 'Illegal mix of collations' I think thats because paymentMethod is VARCHAR and payment is an enum, but I don't know how to solve it.

I hope you can guide me in the right direction.

Thank you!

CodePudding user response：

if the default character set is not identical with the one you use in your tabem, you have to define all variables, with then charset and collation that you use or at least one similar to that

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)

This payment method is already associated to this ID

db<>fiddle here