syntax error with mySQL script with Trigger

http://stackoverflow.com/questions/5814153/error-in-your-sql-syntax-creating-mysql-trigger

remove ; after end


DELIMITER //
CREATE TRIGGER `inst_date` BEFORE INSERT ON `basestation`
FOR EACH ROW BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END//
DELIMITER ;

Hi,

Removed the ; after end. But still the same error

- take out the single quote in inst_date and basestation:

CREATE TRIGGER inst_date BEFORE INSERT ON basestation
FOR EACH ROW BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END;

- take out the ; after END too:

DELIMITER //
CREATE TRIGGER inst_date BEFORE INSERT ON basestation
FOR EACH ROW BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END
//
DELIMITER;

nope, this not working

DELIMITER //
CREATE TRIGGER inst_date BEFORE INSERT ON basestation 
FOR EACH ROW BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END//
DELIMITER ;

Select allOpen in new window

this is the current error i'm getting

 

- the syntax looks ok, but i suspect the single quote might be the reason. in your earlier code, did you copy paste the single quote or type it?

DELIMITER //
CREATE TRIGGER 'inst_date' BEFORE INSERT ON 'basestation' 
FOR EACH ROW BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END;//
DELIMITER;

Select allOpen in new window

- if you run the create table section only without the trigger does it work?

- change the begin to be on next line:

DELIMITER //
CREATE TRIGGER inst_date BEFORE INSERT ON basestation
FOR EACH ROW 
BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END;//
DELIMITER;

Select allOpen in new window

Yes, it works without the trigger.

Putting the begin on a new line didn't work.

my current code below

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE TABLE IF NOT EXISTS `basestation`
 (
  `id` int(11) NOT NULL  AUTO_INCREMENT,
  `imei` int(11) NOT NULL,
  `type` int(11) NOT NULL DEFAULT 0,
  `Install_Date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`),
  CONSTRAINT `basestation_ibfk_1` FOREIGN KEY (`type`) REFERENCES `basestation_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER //
CREATE TRIGGER inst_date BEFORE INSERT ON basestation 
FOR EACH ROW 
BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END//
DELIMITER ;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Select allOpen in new window

- test if u can run the trigger separately without the delimiter:

CREATE TRIGGER inst_date BEFORE INSERT ON basestation
FOR EACH ROW
BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END;

I've just ran this in PHPmyAdmin and it works.

Then copied it you my file, and it fails on syntax error again!!!! GGRRR

Has everyone been stumped?

not yet. taking a timeout watching priest movie just now ;)

- ok do this. take out the delimiter part from the script and run it again.

Hi,

Not sure what your asking me to do.

If i take out the trigger from the file (which contains all the scripts) it works.
If I put the trigger script into the SQL query part of PHPmyAdmin it works fine.
See the last screen shot I did.

I'm going home now, but will be in the office tomorrow (yes, working Saturday)

Cheers

Nick

- ok lets continue tomorrow.
- i suspect might it be the delimiter?:

CREATE TABLE IF NOT EXISTS `basestation`
 (
  `id` int(11) NOT NULL  AUTO_INCREMENT,
  `imei` int(11) NOT NULL,
  `type` int(11) NOT NULL DEFAULT 0,
  `Install_Date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`),
  CONSTRAINT `basestation_ibfk_1` FOREIGN KEY (`type`) REFERENCES `basestation_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

delimiter $$
CREATE TRIGGER inst_date BEFORE INSERT ON basestation 
FOR EACH ROW 
BEGIN
    IF NEW.Install_Date = '0000-00-00 00:00:00' THEN
        SET NEW.Install_Date = NOW();
    END IF;
END$$
delimiter ;

Select allOpen in new window

Nope, still got an error.

I'm thinking maybe the problem down to the .net mySQL connector.

I'm going to changed direction and forget the trigger.

 
thanks

Nick

Blow me over, it worked without the delimiter.!!!!!!
Go figure...

Many thanks.

You got the points :)

- great! :) since you are running it on .net mySQL connector, it did understand the delimiter.

i mean it might did not understand the delimiter.