mysql - 触发器从源表和另一个表插入数据

Question

我正在使用 mysql 5.5，我正在尝试定义一个触发器。

这是我的表格。

+------Table1------+ SOURCE: The one which pulls the trigger+ id | post_title  ++------------------++ 1  | title1      ++ 2  | title2      ++------------------+

+------Table2-------+ Where I need to get the information. + id  | nicename    + Note: Table1.post_title = Table2.nicename+-------------------++ 99  | title1      ++ 86  | title2      ++-------------------+

+----------Table3---------------+ DESTINATION: Where I need to insert+ id  | meta_key  | meta_value  + Note: Table1.id = Table3.meta_value+-------------------------------+       Table2.id= Table3.id+ 99  | hub_page  |        1    ++ 86  | hub_page  |        2    ++-------------------------------+

This is the query I wrote but it returns: #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 6

DELIMITER $$
CREATE TRIGGER 'trigger_name' AFTER INSERT ON 
'table1' FOR EACH ROW 
BEGIN
    DECLARE user_id int;
    SELECT id into user_id from table2 where(table2.nicename = new.post_title);
    INSERT INTO table3(id,metakey,metavalue) VALUES(user_id,'hub_page',new.id);
END$$;

Answer 1

根据您的触发器定义，它说在 table3 上插入后运行它，但在您的问题中，您似乎需要在表 1 上插入后运行它，对 table1 的评论拉动触发器的那个 所以我假设当插入发生在表 1 上时，您需要在表 3 中插入​​一行，而在触发器中，您需要从表 2 中获取数据，为此您可以将触发器重写为

CREATE TRIGGER new_table1_trigger
AFTER INSERT ON 
`Table1` FOR EACH ROW 
INSERT INTO table3(id,meta_key,meta_value) VALUES(
  (SELECT id  from table2 where table2.nicename = new.post_title LIMIT 1),
  'hub_page',
  new.id);  

在下面的演示中，您会注意到我对 table3 使用了任何独立插入，所有插入都是由触发器完成的

Demo

Note you are using trigger for insert only so it will only work for inserts,but if you update the data of table1 then table3 will not be updated for this you will need an update trigger too

Also don't use single quotes for table/column names instead use back-ticks to escape reserved keywords