create or replace trigger SA.X_DID_OPT_AFTER_INSERT
AFTER INSERT ON TABLE_X_DID_OPT
REFERENCING
NEW AS NEW
OLD AS OLD
FOR EACH ROW
DECLARE
ERR_sqlcode number;
ERR_sqlerrm varchar2(256);
t_instr_data_objid number;
t_instr_data_objid_1 number;
CURSOR C1 IS
SELECT TXID.* FROM TABLE_X_INSTR_DATA TXID
WHERE TXID.X_INSTR_DATA2X_INSTRUCTION = :NEW.X_PARENT_PRODUCT_OBJID
AND TXID.X_SCHEDULE_OBJID = :NEW.X_SCHEDULE_OBJID
AND TXID.X_INSTR_PARAM = :NEW.X_PARENT_TN
ORDER BY TXID.X_SEQUENCE;
CURSOR C2(INSTR_DATA_OBJID NUMBER) IS
SELECT TXIV.* FROM TABLE_X_INSTR_VALUE TXIV
WHERE TXIV.X_IV2X_INSTR_DATA = INSTR_DATA_OBJID
ORDER BY TXIV.X_SEQ_NO;
BEGIN
IF :NEW.X_STATUS = 0 THEN
-- UPDATE TABLE_X_DID_OPT B SET B.X_STATUS = 1 WHERE B.OBJID = :NEW.OBJID;
FOR CR1 IN C1 LOOP select SEQ_X_INSTR_DATA.NEXTVAL
into t_instr_data_objid_1
from dual ; select (power(2,28) + t_instr_data_objid_1)
into t_instr_data_objid
from dual ;
INSERT INTO TABLE_X_INSTR_DATA(OBJID,DEV,X_INSTR_PARAM,X_INSTANCE_ID,
X_INTF_OBJID,X_INTF_TYPE,X_STATUS,X_NOTES,X_SEQUENCE,X_INSTR_DATA2X_PRODUCT,
X_INSTR_DATA2X_INSTRUCTION,X_GROUP,X_PACKAGE_NO,X_SCHEDULE_OBJID,X_ASYNC,
X_INSTRUCTION,X_ARCH_IND,X_ISMAIN)
VALUES(t_instr_data_objid,CR1.DEV,:NEW.TN_VALUE,CR1.X_INSTANCE_ID,
CR1.X_INTF_OBJID,CR1.X_INTF_TYPE,CR1.X_STATUS,CR1.X_NOTES,CR1.X_SEQUENCE,:NEW.X_PRODUCT_OBJID,
CR1.X_INSTR_DATA2X_INSTRUCTION,CR1.X_GROUP,CR1.X_PACKAGE_NO,CR1.X_SCHEDULE_OBJID,CR1.X_ASYNC,
CR1.X_INSTRUCTION,CR1.X_ARCH_IND,CR1.X_ISMAIN);
OPEN C2(CR1.OBJID);
FOR CR2 IN C2(CR1.OBJID) LOOP IF CR2.X_NAME = 'an_id' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.X_AN_ID,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'an_seq' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.X_AN_SEQ,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'ven' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.X_D_STRINGFIELD_1,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'tn' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.TN_VALUE,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'external_id' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.TN_VALUE,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSE
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,CR2.X_VALUE,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
END IF;
END LOOP;
CLOSE C2;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
ERR_sqlcode:=SQLCODE;
ERR_sqlerrm:=SUBSTR(SQLERRM,1,255);
INSERT INTO TABLE_X_ERROR_MESSAGE (OBJID, DEV, X_USER_ERR_NO, X_SYS_ERR_NO, X_SYS_ERR_DESC,
X_APPLICATION, X_FUNCTION, X_CASE_ID, X_CUST_ID, X_SERV_INST,
X_DATA_LOG, X_STATUS, X_SOLUTION_LOG, X_INSTER_TIME)
VALUES(POWER(2,28)+SEQ_X_ERROR_MESSAGE.NEXTVAL, NULL, 'X_DID_OPT_AFTER_INSERT_ERROR', TO_CHAR(ERR_SQLCODE), ERR_SQLERRM,
'TRIGGER', 'X_DID_OPT_AFTER_INSERT', NULL, :NEW.X_PARENT_TN, :NEW.TN_VALUE,
NULL, NULL, NULL, SYSDATE);
END;
AFTER INSERT ON TABLE_X_DID_OPT
REFERENCING
NEW AS NEW
OLD AS OLD
FOR EACH ROW
DECLARE
ERR_sqlcode number;
ERR_sqlerrm varchar2(256);
t_instr_data_objid number;
t_instr_data_objid_1 number;
CURSOR C1 IS
SELECT TXID.* FROM TABLE_X_INSTR_DATA TXID
WHERE TXID.X_INSTR_DATA2X_INSTRUCTION = :NEW.X_PARENT_PRODUCT_OBJID
AND TXID.X_SCHEDULE_OBJID = :NEW.X_SCHEDULE_OBJID
AND TXID.X_INSTR_PARAM = :NEW.X_PARENT_TN
ORDER BY TXID.X_SEQUENCE;
CURSOR C2(INSTR_DATA_OBJID NUMBER) IS
SELECT TXIV.* FROM TABLE_X_INSTR_VALUE TXIV
WHERE TXIV.X_IV2X_INSTR_DATA = INSTR_DATA_OBJID
ORDER BY TXIV.X_SEQ_NO;
BEGIN
IF :NEW.X_STATUS = 0 THEN
-- UPDATE TABLE_X_DID_OPT B SET B.X_STATUS = 1 WHERE B.OBJID = :NEW.OBJID;
FOR CR1 IN C1 LOOP select SEQ_X_INSTR_DATA.NEXTVAL
into t_instr_data_objid_1
from dual ; select (power(2,28) + t_instr_data_objid_1)
into t_instr_data_objid
from dual ;
INSERT INTO TABLE_X_INSTR_DATA(OBJID,DEV,X_INSTR_PARAM,X_INSTANCE_ID,
X_INTF_OBJID,X_INTF_TYPE,X_STATUS,X_NOTES,X_SEQUENCE,X_INSTR_DATA2X_PRODUCT,
X_INSTR_DATA2X_INSTRUCTION,X_GROUP,X_PACKAGE_NO,X_SCHEDULE_OBJID,X_ASYNC,
X_INSTRUCTION,X_ARCH_IND,X_ISMAIN)
VALUES(t_instr_data_objid,CR1.DEV,:NEW.TN_VALUE,CR1.X_INSTANCE_ID,
CR1.X_INTF_OBJID,CR1.X_INTF_TYPE,CR1.X_STATUS,CR1.X_NOTES,CR1.X_SEQUENCE,:NEW.X_PRODUCT_OBJID,
CR1.X_INSTR_DATA2X_INSTRUCTION,CR1.X_GROUP,CR1.X_PACKAGE_NO,CR1.X_SCHEDULE_OBJID,CR1.X_ASYNC,
CR1.X_INSTRUCTION,CR1.X_ARCH_IND,CR1.X_ISMAIN);
OPEN C2(CR1.OBJID);
FOR CR2 IN C2(CR1.OBJID) LOOP IF CR2.X_NAME = 'an_id' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.X_AN_ID,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'an_seq' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.X_AN_SEQ,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'ven' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.X_D_STRINGFIELD_1,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'tn' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.TN_VALUE,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSIF CR2.X_NAME = 'external_id' THEN
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,:NEW.TN_VALUE,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
ELSE
INSERT INTO TABLE_X_INSTR_VALUE(OBJID,DEV,X_VALUE,X_NAME,X_SEQ_NO,X_IV2X_INSTR_DATA,X_VALUE_PARAM)
VALUES(POWER(2,28)+SEQ_X_INSTR_VALUE.NEXTVAL,CR2.DEV,CR2.X_VALUE,CR2.X_NAME,
CR2.X_SEQ_NO,t_instr_data_objid,CR2.X_VALUE_PARAM);
END IF;
END LOOP;
CLOSE C2;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
ERR_sqlcode:=SQLCODE;
ERR_sqlerrm:=SUBSTR(SQLERRM,1,255);
INSERT INTO TABLE_X_ERROR_MESSAGE (OBJID, DEV, X_USER_ERR_NO, X_SYS_ERR_NO, X_SYS_ERR_DESC,
X_APPLICATION, X_FUNCTION, X_CASE_ID, X_CUST_ID, X_SERV_INST,
X_DATA_LOG, X_STATUS, X_SOLUTION_LOG, X_INSTER_TIME)
VALUES(POWER(2,28)+SEQ_X_ERROR_MESSAGE.NEXTVAL, NULL, 'X_DID_OPT_AFTER_INSERT_ERROR', TO_CHAR(ERR_SQLCODE), ERR_SQLERRM,
'TRIGGER', 'X_DID_OPT_AFTER_INSERT', NULL, :NEW.X_PARENT_TN, :NEW.TN_VALUE,
NULL, NULL, NULL, SYSDATE);
END;
into t_instr_data_objid_1
from dual ; select (power(2,28) + t_instr_data_objid_1)
into t_instr_data_objid
from dual ;
这里没有错误,dual表中,只有一条数据