- 项目中有个表触发器,上线前执行该trigger脚本时,始终报如下错误:
21:16:28 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State:
42000] ORA-00900: 无效 SQL 语句 … 2 statement(s) executed, 0 row(s)
affected, exec/fetch time: 0.114/0.000 sec [0 successful, 1
warnings, 1 errors]
- trigger脚本如下(该SQL是直接从现有环境复制下来的,肯定没有问题):
CREATE OR REPLACE TRIGGER "WECHAT"."WXUSERLOGS"
AFTER UPDATE OR INSERT ON WEIXIN_USER
FOR EACH ROW
BEGIN
INSERT INTO WEIXIN_LOG("ID",OPEN_ID,USER_NAME,ACCT_OPEN_ID,"GROUP_ID")
values (:new."ID",:new.OPEN_ID,:new.USER_NAME,:new.ACCT_OPEN_ID,:new."GROUP_ID");
END;
-
问题原因:
数据库连接工具对脚本结束符解释不一致所致。后使用Navicat工具可正常执行,使用Dbvisualizer工具则需要再SQL前后添加 --/ 符号。 -
解决办法:
在脚本前后分别加班 --/ 符号,如下:
--/
CREATE OR REPLACE TRIGGER "WECHAT"."WXUSERLOGS"
AFTER UPDATE OR INSERT ON WEIXIN_USER
FOR EACH ROW
BEGIN
INSERT INTO WEIXIN_LOG("ID",OPEN_ID,USER_NAME,ACCT_OPEN_ID,"GROUP_ID")
values (:new."ID",:new.OPEN_ID,:new.USER_NAME,:new.ACCT_OPEN_ID,:new."GROUP_ID");
END;
--/