为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM Database Server 64 V8
【操作系统】:windows10
【CPU】:
【问题描述】*:触发器编译报错:第 5 行, 第 4 列[FOR]附近出现错误: 语法分析出错,请看下是什么原因
CREATE OR REPLACE TRIGGER "DI_QC_THREE"."TR_UPDATE_XNH_BI_LOG_DV_TAG"
AFTER UPDATE OF DV_TAG
ON QC_BPID_BI_LOG
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (:Old.CLASS_CODE = 'X0100')
DECLARE
O_V_DV_TAG INTEGER;
N_V_DV_TAG INTEGER;
V_TABLE_CODE VARCHAR2(100);
V_USER_SCHEMA VARCHAR2(100);
V_BATCH_NUM VARCHAR2(100);
V_SQL VARCHAR2(2000);
BEGIN
N_V_DV_TAG := :New.DV_TAG;
O_V_DV_TAG := :Old.DV_TAG;
V_USER_SCHEMA := :New.USER_SCHEMA;
V_TABLE_CODE := :New.TABLE_CODE;
V_BATCH_NUM := :New.BATCH_NUM;
IF (O_V_DV_TAG = 0 AND N_V_DV_TAG = 1) THEN
V_SQL := 'MERGE INTO QC_DV_XNH_INDEX A ' ||
'USING (SELECT DOMAIN_CODE, BUSINESS_ID FROM ' || V_USER_SCHEMA || '.' || V_TABLE_CODE || ' WHERE BATCH_NUM = ''' || V_BATCH_NUM || ''') B ' ||
'ON (A.DOMAIN_CODE = B.DOMAIN_CODE AND A.TABLE_CODE = ''' || V_TABLE_CODE || ''' AND A.BUSINESS_ID = B.BUSINESS_ID) ' ||
'WHEN NOT MATCHED THEN ' ||
'INSERT (DOMAIN_CODE, TABLE_CODE, BUSINESS_ID) VALUES (B.DOMAIN_CODE, ''' || V_TABLE_CODE || ''', B.BUSINESS_ID)';
EXECUTE IMMEDIATE V_SQL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

调整 REFERENCING NEW AS New OLD AS Old 为 REFERENCING OLD AS Old NEW AS New 即可编译成功。