Comparing one column to another column in different tables and inputting the value into another column trigger MySQL

问题: I have a table called membership. One column is called amount_paid and the other is called valid_membership. I have another table called m_type with a column called price....

问题:

I have a table called membership. One column is called amount_paid and the other is called valid_membership. I have another table called m_type with a column called price. The linking clause is WHERE membership.type_id = m_type.type_id

I want a trigger before insert on the membership table to check if amount paid is >= 1/2 of price. If it is greater i want 1 to be placed in valid_membership else if it isn't true then 0 to be placed in valid_membership.

Just having a little trouble with the correct syntax, This is what i have tried already--

DELIMITER
 //
CREATE TRIGGER valid_membership
    BEFORE INSERT ON membership
    FOR EACH ROW
        BEGIN
        IF (NEW.amount_paid >= 1/2 price) THEN
    SET valid_membership = '1' ELSE '0'
    WHERE membership.type_id = m_type.type_id
  END IF;
END
//
DELIMITER ;

回答1:

DELIMITER //

CREATE TRIGGER valid_membership
BEFORE INSERT ON membership
FOR EACH ROW
BEGIN
  SELECT
    price INTO @price
  FROM
    m_type
  WHERE
    type_id = NEW.type_id
    AND ... -- something missing here as linking by the type_id only is too wide, imho
  ;

  SET NEW.valid_membership = IF(NEW.amount_paid >= @price / 2, 1, 0);
END
//
DELIMITER ;
  • 发表于 2019-03-27 07:23
  • 阅读 ( 214 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除