mysql - 如何使用 if else 语句修复 mysql 触发器？

Question

我有表 up_files 和 fgusers3。触发器应该在插入 up_files 后更新 fgusers3。

up_file:id、uname、天数、d_leave
fgusers3: id,姓名,AnualLeave,cf

我尝试过没有 if else 语句并且工作正常。每次用户插入数据时，触发器都会更新 fgusers3 表中的 AnualLeave 行和 cf 行。

DROP TRIGGER IF EXISTS `after_insert`;
CREATE DEFINER=`root`@`localhost` 
TRIGGER `after_EL` BEFORE INSERT ON `up_files` 
FOR EACH ROW 
UPDATE fgusers3 
SET fgusers3.AnualLeave = fgusers3.AnualLeave - NEW.days + fgusers3.cf,fgusers3.cf = 0 
WHERE fgusers3.name = NEW.uname AND NEW.d_leave = 'Emergency Leave'

添加 if else 语句后，出现错误。请帮助我刚接触 mysql 触发器:

DELIMITER #
CREATE TRIGGER `after_insert` BEFORE INSERT ON `up_files` FOR EACH ROW 
BEGIN

UPDATE fgusers3 
IF(fgusers3.cf < NEW.days ) THEN
         SET fgusers3.AnualLeave = fgusers3.AnualLeave - NEW.days +fgusers3.cf,fgusers3.cf = 0
       WHERE fgusers3.name = NEW.uname AND NEW.d_leave = 'Emergency Leave' ;

       ELSE
       SET fgusers3.cf = fgusers3.cf - NEW.days
       WHERE fgusers3.name = NEW.uname AND NEW.d_leave = 'Emergency Leave' ;
       END IF ;
END#
DELIMITER ; 

说明
我尝试设置，如果用户插入“紧急休假”，天数将扣除年假或从去年结转(cf)(如果有)。如果可用的cf比一天足够，那么将从cf中扣除，否则将从年假中扣除

Answer 1

我认为这更像你想要的。

DELIMITER $$ 
CREATE TRIGGER test_trig BEFORE INSERT ON up_files 
FOR EACH ROW 
bEGIN   
    IF(fgusers3.cf < NEW.days ) THEN
    UPDATE fgusers3 
        SET fgusers3.AnualLeave = fgusers3.AnualLeave - NEW.days +fgusers3.cf,fgusers3.cf = 0 
        WHERE fgusers3.name = NEW.uname AND NEW.d_leave = 'Emergency Leave' ; 
    ELSE 
    UPDATE fgusers3
        SET fgusers3.cf = fgusers3.cf - NEW.days 
        WHERE fgusers3.name = NEW.uname AND NEW.d_leave = 'Emergency Leave' ; 
    END if;
end $$

delimiter ; 

如果没有，那么问题中的样本数据作为文本会有所帮助。