Error importing a previously exported mysql trigger

Question

I had a mysql trigger that has been working, I exported it and removed it and am trying to put it back, but I keep running into the following error:

#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 '' at line 12

My trigger is:

    CREATE TRIGGER `accounts_tracking` AFTER UPDATE ON `accounts`
 FOR EACH ROW BEGIN
IF( NEW.`check_level` != OLD.`check_level` ) THEN
INSERT INTO `accounts_tracking` ( `change_type`, `account_id`, `field`, `old_int`, `new_int`, `old_time`, `new_time` )
VALUES
( "1", 
 OLD.id, 
 "check_level",
 OLD.`check_level`, 
 NEW.`check_level`, 
 UNIX_TIMESTAMP(), 
 UNIX_TIMESTAMP());
END IF;
END

Line #12 is the 2nd UNIX_TIMESTAMP()

My table structure is as follows:

CREATE TABLE `accounts_tracking` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` smallint(5) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `field` varchar(255) NOT NULL,
  `old_int` int(11) NOT NULL,
  `new_int` int(11) NOT NULL,
  `new_time` int(10) unsigned NOT NULL,
  `old_time` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Server type: MySQL Server version: 5.1.73-log

Thanks.

Answer 1

As barranka suggested in comments section, you need to enclose this trigger in a delimiter, like so:

DELIMITER $$
    CREATE TRIGGER `accounts_tracking` AFTER UPDATE ON `accounts`
 FOR EACH ROW BEGIN
IF( NEW.`check_level` != OLD.`check_level`) THEN
INSERT INTO `accounts_tracking` ( `change_type`, `account_id`, `field`, `old_int`, `new_int`, `old_time`, `new_time` )
VALUES
( "1", 
 OLD.id, 
 "check_level",
 OLD.`check_level`, 
 NEW.`check_level`, 
 UNIX_TIMESTAMP(), 
 UNIX_TIMESTAMP());
END IF;
END$$
DELIMITER ;

The reason is that by adding a Begin and End to the statement you are essentially creating a stored routine/procedure with the trigger itself. In order to run multiple statements, like in stored routine/procedure, you need to add delimiters.

In other cases where you do not have the Begin and End within the trigger, you do not need the delimiters. For Example:

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;