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

Answer 1

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.

Answer 2

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.