syntax error with mySQL script with Trigger

I've got a script in a file , while I'm running in .net using the mySQL connector.
It was all working file, until I tried to add a trigger. Now I keep getting syntax error  Error message
/*!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 */;

Open in new window

Nick PriceTechnical ManagerAsked:
Who is Participating?
 
OP_ZaharinCommented:
- can u run it without the delimiter? Just the table script and trigger script together.

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;

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;

0
 
Pratima PharandeCommented:
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 ;
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Nick PriceTechnical ManagerAuthor Commented:
Hi,

Removed the ; after end. But still the same error

0
 
OP_ZaharinCommented:
- 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;
0
 
OP_ZaharinCommented:
- 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;
0
 
Nick PriceTechnical ManagerAuthor Commented:
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 ;

Open in new window

0
 
Nick PriceTechnical ManagerAuthor Commented:
this is the current error i'm getting

 error
0
 
OP_ZaharinCommented:
- 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;

Open in new window


- if you run the create table section only without the trigger does it work?
0
 
OP_ZaharinCommented:
- 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;

Open in new window

0
 
Nick PriceTechnical ManagerAuthor Commented:
Yes, it works without the trigger.
0
 
Nick PriceTechnical ManagerAuthor Commented:
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 */;

Open in new window

0
 
OP_ZaharinCommented:
- 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;
0
 
Nick PriceTechnical ManagerAuthor Commented:
I've just ran this in PHPmyAdmin and it works.

Then copied it you my file, and it fails on syntax error again!!!! GGRRR working in PHPmyAdmin
0
 
Nick PriceTechnical ManagerAuthor Commented:
Has everyone been stumped?
0
 
OP_ZaharinCommented:
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.
0
 
Nick PriceTechnical ManagerAuthor Commented:
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
0
 
OP_ZaharinCommented:
- 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 ;

Open in new window

0
 
Nick PriceTechnical ManagerAuthor Commented:
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.

 error
thanks

Nick
0
 
Nick PriceTechnical ManagerAuthor Commented:
Blow me over, it worked without the delimiter.!!!!!!
Go figure...

Many thanks.

You got the points :)
0
 
OP_ZaharinCommented:
- great! :) since you are running it on .net mySQL connector, it did understand the delimiter.
0
 
OP_ZaharinCommented:
i mean it might did not understand the delimiter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.