CODEWITHSUNDEEP
mysql
3ashmawy
3ashmawy

Reputation: 469

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 ;

It outputs the following error :

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

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.

Upvotes: 1

Views: 3484

Answers (1)

3ashmawy
3ashmawy

Reputation: 469

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

Thanx anyway for your views :)

Upvotes: 2

Related Questions