在 mysql 中转换 mssql 触发器

Question

我刚刚从MSSQL转移到MySQL，我现在正在做的是将mssql中的所有触发器转换为MySQL，我找到了困难。我正在插入记录到名为 pp_change 的表中，完成后应更新一个名为 profile 的表，最后将记录插入photo表中

DELIMITER $$
CREATE TRIGGER pic_pic AFTER INSERT ON pp_change
FOR EACH ROW
BEGIN      
BEGIN             
    UPDATE profile           
    SET  profile_pix = pp_change.pic           
    FROM Inserted pp_change          
   WHERE profile.email = pp_change.email          
INSERT INTO photos (email,pic,wardrobe,upload_type,pic_view,up_user_id,country,time_group,fpage)                            
SELECT (email,pic,wardrobe,upload_type,pic_view,user_id,country,time_group,fpage) 
from pp_change             
END$$
DELIMITER ;

当我执行时，我收到此错误

#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 'FROM Inserted pp_change 

Answer 1

MySQL没有inserted的概念。 它使用 new 变量来存储新值。 因此，您应该使用它们，而不是原始表：

DELIMITER $$
CREATE TRIGGER pic_pic AFTER INSERT ON pp_change
FOR EACH ROW
BEGIN      
BEGIN             
    UPDATE profile           
        SET  profile_pix = new.pic           
        WHERE profile.email = new.email ;         
    INSERT INTO photos (email, pic, wardrobe, upload_type, pic_view, up_user_id, country, time_group, fpage)                            
        SELECT new.email, new.pic, new.wardrobe, new.upload_type, new.pic_view, new.user_id, new.country, new.time_group, new.fpage;
END$$
DELIMITER ;