sql trigger to set field value from another field

问题: I am trying to set trigger to the sql to update value of a gf with a value from field "title" inside table c_p_r, but getting an error in syntax. Can someone point me where...

问题:

I am trying to set trigger to the sql to update value of a gf with a value from field "title" inside table c_p_r, but getting an error in syntax. Can someone point me where I am wrong?

  CREATE TRIGGER setgf
    AFTER UPDATE ON c_p_r
    FOR EACH ROW BEGIN
      TRUNCATE gf
      UPDATE gf
      SET gf = title
      FROM c_p_r

This thing is answering me with

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE gf
  SET gf = title
  FROM c_p_r' at line 5

Can someone point me where I am wrong with it?


回答1:

Your UPDATE sql query is malformed. Also if you are trying to copy one field value to another field during update. You should use BEFORE UPDATE handler. I would write the query like below:

 CREATE TRIGGER setgf
    BEFORE UPDATE ON c_p_r
    FOR EACH ROW BEGIN
      SET NEW.gf = COALESCE(NEW.gf, NEW.title); //this will take care of not overriding the gf if it was part of update statement
    END
  • 发表于 2018-07-05 11:57
  • 阅读 ( 285 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除