创建触发器报错

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】社区版4.0
【问题描述】执行如下创建触发器语句，该语句在mysql执行成功
DELIMITER /
create TRIGGER portal_user_navigation_insert AFTER INSERT on
portal_user_navigation
BEGIN
INSERT INTO
rqjcpt_qianmo_sys_db.portal_user_navigation
VALUES
( – new.USER_ID
– , new.APP_ID
new.SORT_NUM
, new.SHOW_IN_MAIN
, new.USER_ID
, new.APP_ID);
UPDATE rqjcpt_qianmo_sys_db.portal_user_navigation A
INNER JOIN
(SELECT
B.ID ,
B.OLD_ID
FROM
rqjcpt_qianmo_sys_db.sys_user B) AS t
ON A.OLD_USER_ID = t.OLD_ID SET A.USER_ID = t.ID
WHERE A.OLD_USER_ID = new.USER_ID;

    UPDATE `rqjcpt_qianmo_sys_db`.`portal_user_navigation` A 
  INNER JOIN 
    (SELECT 
      B.ID ,
      B.OLD_ID 
    FROM
     `rqjcpt_qianmo_sys_db`.`portal_navigation` B) AS t 
    ON A.OLD_APP_ID = t.OLD_ID  SET A.`APP_ID` = t.ID
    WHERE  A.OLD_APP_ID = new.APP_ID;
END
	/
	DELIMITER ;

【复现路径】问题出现前后相关操作
【问题现象及影响】
1064 - You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘BEGIN’ at line 3
【附件】

#3

麻烦整理下格式吧，有点乱了

#4

DELIMITER /
CREATE TRIGGER portal_user_navigation_insert AFTER INSERT ON portal_user_navigation
BEGIN

INSERT INTO rqjcpt_qianmo_sys_db.portal_user_navigation
VALUES
	(new.APP_ID new.SORT_NUM, new.SHOW_IN_MAIN, new.USER_ID, new.APP_ID );
UPDATE rqjcpt_qianmo_sys_db.portal_user_navigation A

INNER JOIN ( SELECT B.ID, B.OLD_ID FROM rqjcpt_qianmo_sys_db.sys_user B ) AS t ON A.OLD_USER_ID = t.OLD_ID 
SET A.USER_ID = t.ID 
WHERE
	A.OLD_USER_ID = new.USER_ID;

UPDATE `rqjcpt_qianmo_sys_db`.`portal_user_navigation` A
INNER JOIN ( SELECT B.ID, B.OLD_ID FROM `rqjcpt_qianmo_sys_db`.`portal_navigation` B ) AS t ON A.OLD_APP_ID = t.OLD_ID 
SET A.`APP_ID` = t.ID 
WHERE
	A.OLD_APP_ID = new.APP_ID;

END /

DELIMITER;

#5

你好，已格式化

#6

你这个也没有个前后文，比如这个表结构什么的，也没办法帮你复现，但你的报错很明显提醒是语法错误，从文本上浏览

image
image1290×330 29 KB

有没有可能是这种间距之类的格式没有写好，可以网上找个sql格式器，在里面写出比较规整的sql，整个文本看起来也不长， 也就三个主要sql， 你可以先把这三个单个单个拆开在ob 里面尝试有没有错误，没有错误再串起来