error in trigger creation in phpmyadmin

Question

I created the very simple triger and I think syntex is also correct:

    CREATE TRIGGER trig1 after INSERT ON urlcontent for each row
BEGIN
    insert into userpost(userid,url,hash) values (userid,url,hash);
END;

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

Structure of both table:

CREATE TABLE urlcontent (
  userid text NOT NULL,
  url varchar(255) NOT NULL,
  `desc` varchar(2048) NOT NULL,
  preview varchar(255) NOT NULL,
  img_url varchar(128) NOT NULL,
  title varchar(128) NOT NULL,
  `hash` varchar(128) NOT NULL,
  rate varchar(20) DEFAULT NULL,
  `time` varchar(64) DEFAULT NULL,
  sentiment varchar(32) DEFAULT NULL,
  `subject` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table 'userpost'
--

CREATE TABLE userpost (
  userid varchar(40) NOT NULL DEFAULT '',
  url varchar(255) DEFAULT NULL,
  `desc` varchar(2048) DEFAULT NULL,
  preview varchar(255) DEFAULT NULL,
  img_url varchar(128) DEFAULT NULL,
  title varchar(128) DEFAULT NULL,
  `hash` varchar(128) NOT NULL DEFAULT '',
  rate varchar(16) DEFAULT NULL,
  `time` varchar(64) DEFAULT NULL,
  pcount varchar(16) DEFAULT NULL,
  ncount varchar(16) DEFAULT NULL,
  isset varchar(16) DEFAULT NULL,
  sentiment varchar(32) DEFAULT NULL,
  `subject` varchar(64) DEFAULT NULL,
  PRIMARY KEY (userid,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

hash and url are key in userpost table/

Structure of both table

values are coming from user, like in registration table. I mean user insert these values into urlcontent. And on insert I want same to be inserted into userpost table — Aamirkhan, Dec 05 '13 at 14:05

Filipe Silva · Accepted Answer · 2013-12-05T14:20:39.313

4

Your specific error is likely due to the delimiter being defined as ;.

If you change the delimiter to | (in the box below the query editor) like in the following image it will work:

enter image description here

This helps the trigger to be created without stoping the query at the first ;.

This would work, but to insert the values from urlcontent into userpost you have to add the keywords NEW before the values. This tells the trigger that the values you want to insert in userpost are the ones that were just inserted into urlcontent:

CREATE TRIGGER trig1 AFTER INSERT ON urlcontent 
FOR EACH ROW
BEGIN
    insert into userpost(userid,url,hash) values (NEW.userid,NEW.url,NEW.hash);
END;
|

edited Dec 05 '13 at 14:20

answered Dec 05 '13 at 14:05

Filipe Silva

21,189
5
53
68

did you change the delimiter to |? i tested it here and it worked – Filipe Silva Dec 05 '13 at 14:10
yes, but first you have to change the delimiter to | in the box below the query box, like in this example: http://i.imgur.com/zrf6fbK.png – Filipe Silva Dec 05 '13 at 14:15
Thanks for image. Worked as expected – Aamirkhan Dec 05 '13 at 14:18

error in trigger creation in phpmyadmin

1 Answers1