mysql - 插入和计算之前的数据库触发器

Question

所以我有这些表:

CREATE TABLE `chittytransactions` (
  `ChittyTransactionID` int(11) NOT NULL,
  `AuctionID` int(11) NOT NULL,
  `ChittyAccNo` int(11) DEFAULT NULL,
  `Date` datetime DEFAULT NULL,
  `Amount` double DEFAULT NULL,
  `Description` varchar(50) DEFAULT NULL,
  `TransRefence` varchar(50) DEFAULT NULL COMMENT 'Reference from actual Bank transaction',
  `TransStatus` tinyint(1) DEFAULT NULL COMMENT 'If Transaction Pending or Cleared',
  `ClearanceDate` datetime DEFAULT NULL,
  `PaymentMethod` int(1) DEFAULT NULL COMMENT '0- Cash, 1- bank transfer, 2- personal credit etc'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `chittyusers` (
  `ChittyAccNo` int(11) NOT NULL,
  `UserId` int(11) NOT NULL,
  `ChittyID` int(11) NOT NULL,
  `LatePaymentFee` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `UserId` int(11) NOT NULL,
  `UserName` varchar(45) NOT NULL,
  `UserNameVerified` tinyint(1) DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我想做的是检查交易日期是否与 chittytransaction 表中的清算日期相同，如果不是，则向 chittyusers 添加滞纳金表(最好是按天计算，按日期逾期付款需加收额外费用)。这应该在插入之前完成，我正在使用触发器。到目前为止我有这个:

ROP TRIGGER IF EXISTS chitty_beforet_trig;

DELIMITER ;;
CREATE TRIGGER chitty_beforet_trig BEFORE Insert ON chittytransaction
FOR EACH ROW
  BEGIN
  DECLARE `mainDate` datetime;
  DECLARE `claredDate` datetime;
  DECLARE `chitAccNo` INT(11);
  DECLARE `userId` INT(11);
  DECLARE `latePay` DOUBLE;
  DECLARE late TINYINT;
  DECLARE cursor1 CURSOR FOR SELECT ChittyAccNo FROM accounting.`chittyusers`;
  DECLARE cursor2 CURSOR FOR SELECT  `Date` FROM accounting.`chittyTransaction`; 
  DECLARE cursor3 CURSOR FOR SELECT  ClearanceDate FROM accounting.`chittyTransaction`;
  DECLARE cursor4 CURSOR FOR SELECT  UserId FROM accounting.`users`;

  OPEN cursor1;
  OPEN cursor2;
  OPEN cursor3;
  OPEN cursor4;

  FETCH cursor1 INTO chitAccNo;
  FETCH cursor2 INTO mainDate
  FETCH cursor3 INTO claredDate;
  FETCH cursor3 INTO userId;
    CASE
        WHEN claredDated <> mainDate THEN
            SET late ='1';
         ELSE
            SET late = '0';
         END;
    END CASE;

    IF late THEN
        UPDATE `chittyusers` SET LatePaymentFee = 50 WHERE UserId = userId;
    END IF;
 CLOSE cursor1;
 CLOSE cursor2;
 CLOSE cursor3;
 CLOSE cursor4;

 END;;
 DELIMITER;

我不断收到许多不同的错误等，我不知道我做得是否正确。以前从未使用过这些，所以有点困难。谁能告诉我我做错了什么，任何解决方案将不胜感激。

Answer 1

天啊，从哪里开始呢？

对于初学者来说，行触发器永远不应该查询它所附加到的表。 (MySQL 允许这样做；许多 DBMS 不允许。)

INSERT 行触发器始终可以访问通过 NEW 伪记录插入的行，其中包含与基础表相同的列。 NEW 的内容是从 INSERT 语句的 VALUES 子句初始化的，以及您对 INSERT 所做的任何更改插入完成后，strong>NEW 将反射(reflect)在表行中。

另一个问题是 chittyusers 上的光标有一个无条件 SELECT，它将抓取表中的每一行，而不仅仅是一行你要。您实际上正在做的是获取 userId 的近乎随机的值，这可能与您想要的值无关。实际上，您甚至不需要为此使用光标；带有 WHERE 子句的简单 INSERT ... INTO 效果会更好:

SELECT `UserId`
  INTO `userId`
  FROM chittyusers
  WHERE ChittyAccNo = NEW.ChittyAccNo;

第三，你的CASE结构是多余的；您可以使用简单的 IF 执行完全相同的操作:

IF NEW.ClearanceDate <> NEW.`Date` THEN
  UPDATE `chittyusers`
    SET LatePaymentFee = 50
    WHERE UserId = userId;
END IF;

我不打算为您编写整个触发器，但这些指针至少应该将错误消息减少到可管理的数量。