• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

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

0
Nick Price
Asked:
Nick Price
  • 10
  • 10
  • 2
1 Solution
 
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
 
Nick PriceTechnical ManagerAuthor Commented:
Hi,

Removed the ; after end. But still the same error

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 10
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now