create or replace TRIGGER MJH_CAD_ERP
AFTER DELETE OR INSERT OR UPDATE ON CAD_0
FOR EACH ROW
declare VY_DRAWING_ID CHAR(38);VCAD_ID1 VARCHAR2(32);VDRAWING_CODE VARCHAR2(30);VDRAWING_NAME VARCHAR2(100);VDRAWING_SPECIFICATION VARCHAR2(2000);
VDRAWING_DESC VARCHAR2(2000);VBI_FILE_ID VARCHAR2(30);VFILE_ID VARCHAR2(32);VFILE_NAME VARCHAR2(100);VFILE_FILEPATH VARCHAR2(2000);VFILE_SIZE NUMBER;VREMARK VARCHAR2(2000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--新增或者更新
if inserting or updating then
VCAD_ID1:=:NEW.GUID; VDRAWING_CODE:=:NEW.ID; VDRAWING_NAME:=:NEW.NAME; VDRAWING_DESC:=:NEW.F_000109;
VY_DRAWING_ID:=sys_guid();
VY_DRAWING_ID:=substr(VY_DRAWING_ID,0,8)||'-'||substr(VY_DRAWING_ID,9,4)||'-'||substr(VY_DRAWING_ID,13,4)||'-'||substr(VY_DRAWING_ID,17,4)||'-'||substr(VY_DRAWING_ID,21,12);
--新增
if inserting then
INSERT into Y_DRAWING@E10database(Y_DRAWING_ID,CAD_ID,DRAWING_CODE,DRAWING_NAME,DRAWING_SPECIFICATION,DRAWING_DESC,REMARK,BI_FILE_ID,FILE_ID,FILE_NAME,FILE_FILEPATH,FILE_SIZE,"ApproveStatus")
values(VY_DRAWING_ID,VCAD_ID1,VDRAWING_CODE,VDRAWING_NAME,'',VDRAWING_DESC,'','','','','',0,'Y');
--更新
else
SELECT C1.F_000087,C1.F_000001 INTO VDRAWING_SPECIFICATION,VREMARK FROM CAD_1 C1 WHERE C1.FOUNDATIONFK=:NEW.GUID;
IF :NEW.FILEGUID IS NOT NULL THEN
SELECT BF.GUID,BF.ID,BF.NAME,BF.FILEPATH,BF.FILESIZE INTO VBI_FILE_ID,VFILE_ID,VFILE_NAME,VFILE_FILEPATH,VFILE_SIZE FROM BI_FILE BF WHERE BF.GUID=:NEW.FILEGUID;
END IF;
UPDATE Y_DRAWING@E10database Y SET Y.DRAWING_NAME=VDRAWING_NAME,
Y.DRAWING_SPECIFICATION=VDRAWING_SPECIFICATION,
Y.DRAWING_DESC=VDRAWING_DESC,
REMARK=VREMARK,
Y.BI_FILE_ID=VBI_FILE_ID,
Y.FILE_ID=VFILE_ID,
Y.FILE_NAME=VFILE_NAME,
Y.FILE_FILEPATH=VFILE_FILEPATH,
Y.FILE_SIZE=VFILE_SIZE
WHERE Y.CAD_ID=VCAD_ID1;
end if;
--删除
else
VCAD_ID1:=:old.GUID;
DELETE FROM Y_DRAWING@E10database Y WHERE Y.CAD_ID=VCAD_ID1;
end if;
commit;
END;
以上触发器是,当INSERT、UPDATE、DELETE时,触发连接SQL SERVICE做相应的操作。当INSERT的时候,当UPDATE、DELETE时就报错:ORACLE ORA-02070: 数据库 E10DATABASE 不支 持此上下文中的 SYS_OP_C2C;折腾了一天都无法解决?求解,不胜感激!!