mysql triggers syntax error

Question

I am having trouble creating a MySQL trigger. The trigger uses replace, so when a row is replaced, it reads the row and saves the information in another table. If a new row is inserted into the tracking table, the information is not saved in the other table, as the row didn't already exist. I just need to save the tracking row if it already exists.

DROP TRIGGER IF EXISTS savetracking;

CREATE TRIGGER savetracking BEFORE REPLACE ON 'orderstest.tracking'
FOR EACH ROW 
BEGIN
DECLARE orderid INTEGER;

IF NEW.invno != '' THEN
SET orderid = (SELECT orderid FROM order_header WHERE invno = NEW.invno);
INSERT INTO comments (id, date_time, type, comments) VALUES 
            (orderid, SYSDATE(), \"O\", \"Previous Tracking: USPS - OLD.trackno\");
ENDIF;
END;

This is the error I get:

#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 'REPLACE ON 'orderstest.tracking' FOR EACH ROW BEGIN DECLARE orderid INTEGER' at line 1

I am using PHPMyAdmin to try and add the trigger.

MySQL version is Software version: 5.0.95-rs

Thanks,

Steve

---
- Table structure for table `tracking`
--

CREATE TABLE IF NOT EXISTS `tracking` (
  `id` int(11) NOT NULL auto_increment,
  `invno` bigint(20) NOT NULL default '0',
  `carrier` varchar(5) NOT NULL default '',
  `trackno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `invno` (`invno`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3033 ;

--
-- Table structure for table `comments`
--

CREATE TABLE IF NOT EXISTS `comments` (
  `comment_id` int(11) NOT NULL auto_increment,
  `id` int(11) NOT NULL default '0',
  `date_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `type` char(1) NOT NULL default '',
  `comments` mediumtext NOT NULL,
  PRIMARY KEY  (`comment_id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10426 ;

--
-- Table structure for table `order_header`
--

CREATE TABLE IF NOT EXISTS `order_header` (
  `our_orderid` int(11) NOT NULL auto_increment,
  `orderid` varchar(20) NOT NULL default '0',
  `orderdatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `custid` int(11) NOT NULL default '0',
  `source` char(1) NOT NULL default '',
  `comments` mediumtext NOT NULL,
  `invno` bigint(20) NOT NULL default '0',
  `infoid` varchar(15) NOT NULL default '0',
  `remote_host` varchar(50) NOT NULL default '',
  `remote_addr` varchar(50) NOT NULL default '',
  `items` int(11) NOT NULL default '0',
  `paytype` varchar(20) NOT NULL default '',
  `cc_name` varchar(50) NOT NULL default '',
  `cc_num` tinyblob NOT NULL,
  `cc_valid` tinyblob NOT NULL,
  `cc_expire` varchar(10) NOT NULL default '',
  `avs_address` varchar(20) NOT NULL default '',
  `avs_zip` varchar(5) NOT NULL default '',
  `shipping` varchar(30) NOT NULL default '',
  `order_status` char(1) NOT NULL default '',
  `batch_no` int(11) NOT NULL default '0',
  `time_process` datetime NOT NULL default '0000-00-00 00:00:00',
  `fraud_flag` char(1) NOT NULL default '',
  `referrer` varchar(255) NOT NULL default '',
  `stats` char(1) NOT NULL default 'N',
  `country_code` char(2) NOT NULL default '',
  `zipzone` tinyint(4) NOT NULL default '0',
  `ship_zip` varchar(5) NOT NULL default '',
  `bank_name` varchar(50) NOT NULL default '',
  `bank_country_name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`our_orderid`),
  KEY `order_status` (`order_status`),
  KEY `orderdatetime` (`orderdatetime`),
  KEY `invno` (`invno`),
  KEY `remote_host` (`remote_host`),
  KEY `custid` (`custid`),
  KEY `infoid` (`infoid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=320081 ;

I have changed the trigger to this.

DROP TRIGGER IF EXISTS savetracking;

CREATE TRIGGER savetracking BEFORE INSERT ON orderstest.tracking
FOR EACH ROW 
BEGIN
DECLARE orderid INTEGER;
DECLARE otrackno INTEGER;

SET otrackno = (SELECT trackno FROM tracking WHERE invno = NEW.invno);
IF otrackno != '' THEN
SET orderid = (SELECT orderid FROM order_header WHERE invno = NEW.invno);
INSERT INTO comments (id, date_time, type, comments) VALUES 
            (orderid, SYSDATE(), 'O', 'Previous Tracking: USPS - otrackno');
DELETE FROM trackno WHERE invno = NEW.invno;

ENDIF;

END;

The error I get now is

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 4

It's a syntax error, so table structure is actually not really relevant. ;-) — GolezTrol, Oct 11 '14 at 22:07
It's probably due to the magic quotes in the line `IF NEW.invno != ‘’ THEN`. Did you type this in or copy this from a word processor? Use normal quotes: `''`. — GolezTrol, Oct 11 '14 at 22:08
Thanks for the suggestion. You were correct with the quotes, I had copied from a Google Doc. I have tried the proper quotes, and I am still getting the same error on the same line at the top. It seems to be choking on the DECLARE? — Steve Barnes, Oct 11 '14 at 22:29

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

There are no REPLACE triggers. You have the choice of INSERT, UPDATE, or DELETE.

If you execute a REPLACE, it will cause both the DELETE and INSERT triggers on the table to run.

Also you can't quote the table name like you are doing.

ON 'orderstest.tracking'

Should be

ON `orderstest`.`tracking`

And you should learn about how to use DELIMITER before you try to define triggers that contain compound statements.

edited May 23 '17 at 10:33

Community

1
1

answered Oct 12 '14 at 01:32

Bill Karwin

538,548
86
673
828

If you read the question, you will see that I am using PHPMyAdmin, so delimiters are not required to be specified. I have tried using INSERT in place of REPLACE, and the error still occurs at the same place. – Steve Barnes Oct 12 '14 at 04:04

score 0 · Answer 2 · answered Oct 13 '14 at 16:58

0

I want to apologize profusely to Bill. He was correct. I needed to use the delimiters even though I was using PHPMyAdmin. He was also correct in that I can't use replace in a trigger. I have now solved the problem by using insert for the trigger and changing the logic within the trigger. Thanks Bill.

answered Oct 13 '14 at 16:58

Steve Barnes

13
3

Thanks, I'm glad to help. By the way, it's customary on StackOverflow to give an upvote to answers that helped you, and click the "accepted answer" checkmark by the answer that turned out to be correct. – Bill Karwin Oct 13 '14 at 17:04

mysql triggers syntax 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 4

2 Answers2