MYSql Trigger statement

Question

I can't seem to get one of the triggers im trying to make to work at the moment it looks like this (has been messed around with alot in an attempt to get it to work so probably doesn't make any sense now)

CREATE TRIGGER `insertproductebay` AFTER INSERT ON `product`
FOR EACH ROW INSERT INTO eBayLinked
(product_id,company_id,eBay_ID,ebay_token_id,ebay_username) 
SELECT ebay_token_id,ebay_username
FROM
'company'
WHERE
'company_id' = 'company_id'
  VALUES
      (NEW.product_id,NEW.company_id,NEW.eBay_ID,ebay_token_id,ebay_username)

you have several problems. One is back-ticks, two is column count — Drew
– Drew, Commented Dec 16, 2015 at 18:39

Drew · Accepted Answer · 2015-12-16 18:49:53Z

1

The following makes it past the 1065 error

Care must be taken to use back-ticks around table and column names, and not to use single quotes there.

drop trigger if exists `insertproductebay`;

DELIMITER $$
CREATE TRIGGER `insertproductebay` AFTER INSERT ON `product`
FOR EACH ROW 
INSERT INTO eBayLinked
(product_id,company_id,eBay_ID,ebay_token_id,ebay_username) 
SELECT NEW.product_id,NEW.company_id,NEW.eBay_ID,ebay_token_id,ebay_username
FROM `company`
WHERE `company_id` = 'company_id'
$$
DELIMITER ;

The remaining problem as I see it could be what are you meaning by

WHERE `company_id` = 'company_id'

in a trigger. Because a trigger is a faceless piece of code that runs in the background, succeeding or failing silently. In other words, there is no user interface associated with it.

edited Dec 16, 2015 at 18:49

answered Dec 16, 2015 at 18:40

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eggyal · Accepted Answer · 2015-12-16 18:40:01Z

0

You can't mix the INSERT ... SELECT and INSERT ... VALUES syntaxes in that way. However, you can provide literal values in a select list, thusly:

CREATE TRIGGER insertproductebay AFTER INSERT ON product FOR EACH ROW
  INSERT INTO eBayLinked
    (product_id, company_id, eBay_ID, ebay_token_id, ebay_username) 
  SELECT NEW.product_id, NEW.company_id, NEW.eBay_ID, ebay_token_id, ebay_username
  FROM   company
  WHERE  company_id = NEW.company_id

answered Dec 16, 2015 at 18:40

eggyal

127k18 gold badges221 silver badges245 bronze badges

1 Comment

Drew Over a year ago

@jamescox welcome to the stack. Please mark this answer as the Accepted Answer by giving it the Green Check Mark under the arrows to the left of the answer. Thanks

Collectives™ on Stack Overflow

MYSql Trigger statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related