CODEWITHSUNDEEP
mysqlphpmyadmin
Aamirkhan
Aamirkhan

Reputation: 5784

error in trigger creation in phpmyadmin

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

Upvotes: 3

Views: 455

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

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

Upvotes: 4

Related Questions