MySQL触发器插入另一个表 [英] MySQL Triggers insert into another table

问题描述

从语法上讲，以下查询出了什么问题:

Syntax-wise , what's wrong with the following query :

-- Trigger DDL Statements
DELIMITER $$

USE `mydb`$$

DROP TRIGGER IF EXISTS annual_scenario;

CREATE TRIGGER annual_scenario 
AFTER INSERT ON quote FOR EACH ROW
BEGIN
  SET @annual_date = (SELECT  NEW.date - INTERVAL 1 YEAR);
  SET @annual =  (SELECT COUNT(DISTINCT Q.date)
                  FROM quote Q, instrument I
                  WHERE Q.date = I.date 
                    AND Q.ticker = I.date
                    AND Q.market = I.date
                    AND I.type != 'bank'
                    AND Q.date =  @annual_date
                    AND Q.ticker = NEW.ticker
                    AND Q.market = NEW.market
                  );
   SET @annual_close = (SELECT Q.close
                        FROM quote Q
                        WHERE Q.ticker = NEW.ticker 
                          AND Q.date = @annual_date
                          AND Q.market = NEW.market
                        );

   SET @ratio = (NEW.close / @annual_close);

    IF @annual = 1 THEN
      INSERT INTO 
        scenario(period,ticker,date_1,date_2,close_1,close_2,ratio)
        VALUES('annual',NEW.ticker,NEW.date,@annual_date,NEW.close,@annual_close,@ratio);
    ENDIF 
END $$
DELIMITER ; 

它输出以下错误:

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TRIGGER annual_scenario 
AFTER INSERT ON quote FOR EACH ROW
BEGIN
  SE' at line 2

我要检查的是年度回报是否为1，然后将新记录和相应的值插入表场景中.

What i am trying to do is checking if annual returns 1 then i would insert a new record into the table scenario with the corresponding values.

推荐答案

显然，MySQL 5.1不使用ENDIF，而是使用END IF.更何况失踪者; END IF和END之后.

Apparently MySQL 5.1 doesn't use ENDIF rather it uses END IF. let alone the missing ; after END IF and END.

仍然感谢您的意见:)

这篇关于MySQL触发器插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！