mysql两个表插入触发器,MySQL触发器插入另一个表

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.

解决方案

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 :)