CREATE OR REPLACE TRIGGER BV.TRIGGER_MR_SHIP_ADDR_INSERT
BEFORE INSERT
ON BV.MR_SHIP_ADDR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
TEMP_OID INTEGER;
TEMP_SALES_ID varchar(10);
TEMP_AGENT_ID VARCHAR2(20);
TEMP_SHIP_ID VARCHAR(20);
TEMP_SHIP_ADDR VARCHAR(200);
type MR_SHIP_ADDR_REC is record(
V_PROD_GRP BV.DC_AGENTBUSIUNIT.BUSI_UNIT_ID%TYPE,
V_SALES_ID BV.DC_AGENTBUSIUNIT.SALESORG_ID%TYPE
);
type MR_SHIP_ADDR_CURSOR is ref cursor return MR_SHIP_ADDR_REC;
MR_SHIP_ADDR_SELECT_CURSOR MR_SHIP_ADDR_CURSOR;
MR_SHIP_ADDR_SELECT_RECORD MR_SHIP_ADDR_REC;
BEGIN
TEMP_OID := :NEW.OID;
TEMP_SHIP_ID := :NEW.SHIP_ALIAS;
TEMP_SHIP_ADDR := :NEW.SHIP_ADDRESS;
SELECT DC_ERP_AGENT_ID INTO TEMP_AGENT_ID FROM MR_ACCT_PROFILE WHERE OID=TEMP_OID; open MR_SHIP_ADDR_SELECT_CURSOR for
SELECT C.BUSI_UNIT_ID,C.SALESORG_ID FROM MR_ACCT_PROFILE A,DC_AGENTBUSIUNIT C WHERE A.ACCOUNT_NAME=C.AGENT_ID
AND OID=TEMP_OID;
IF MR_SHIP_ADDR_SELECT_CURSOR%ISOPEN THEN
LOOP
FETCH MR_SHIP_ADDR_SELECT_CURSOR INTO MR_SHIP_ADDR_REC;
EXIT WHEN MR_SHIP_ADDR_SELECT_CURSOR%NOTFOUND;
INSERT INTO HP_HOMEPC_RT (SALE_GRP,AGENT_ID,SENDER_ID,HPID,PL,ADDRESS,MODIFY_TIME,MODIFY_NAME,CREATE_TIME)
VALUES (MR_SHIP_ADDR_REC.V_SALES_ID,DC_ERP_AGENT_ID,TEMP_SHIP_ID,'',MR_SHIP_ADDR_REC.V_PROD_GRP,TEMP_SHIP_ADDR,SYSDATE,'system',SYSDATE);
END LOOP;
END IF; END TRIGGER_MR_SHIP_ADDR_INSERT;
报错的信息是:29/47 PLS-00321: expression 'MR_SHIP_ADDR_REC' is inappropriate as the left hand side of an assignment statement
29/9 PL/SQL: SQL Statement ignored
32/77 PLS-00330: invalid use of type name or subtype name
32/77 PL/SQL: ORA-00904: "MR_SHIP_ADDR_REC"."V_PROD_GRP": invalid identifier
31/9 PL/SQL: SQL Statement ignored
BEFORE INSERT
ON BV.MR_SHIP_ADDR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
TEMP_OID INTEGER;
TEMP_SALES_ID varchar(10);
TEMP_AGENT_ID VARCHAR2(20);
TEMP_SHIP_ID VARCHAR(20);
TEMP_SHIP_ADDR VARCHAR(200);
type MR_SHIP_ADDR_REC is record(
V_PROD_GRP BV.DC_AGENTBUSIUNIT.BUSI_UNIT_ID%TYPE,
V_SALES_ID BV.DC_AGENTBUSIUNIT.SALESORG_ID%TYPE
);
type MR_SHIP_ADDR_CURSOR is ref cursor return MR_SHIP_ADDR_REC;
MR_SHIP_ADDR_SELECT_CURSOR MR_SHIP_ADDR_CURSOR;
MR_SHIP_ADDR_SELECT_RECORD MR_SHIP_ADDR_REC;
BEGIN
TEMP_OID := :NEW.OID;
TEMP_SHIP_ID := :NEW.SHIP_ALIAS;
TEMP_SHIP_ADDR := :NEW.SHIP_ADDRESS;
SELECT DC_ERP_AGENT_ID INTO TEMP_AGENT_ID FROM MR_ACCT_PROFILE WHERE OID=TEMP_OID; open MR_SHIP_ADDR_SELECT_CURSOR for
SELECT C.BUSI_UNIT_ID,C.SALESORG_ID FROM MR_ACCT_PROFILE A,DC_AGENTBUSIUNIT C WHERE A.ACCOUNT_NAME=C.AGENT_ID
AND OID=TEMP_OID;
IF MR_SHIP_ADDR_SELECT_CURSOR%ISOPEN THEN
LOOP
FETCH MR_SHIP_ADDR_SELECT_CURSOR INTO MR_SHIP_ADDR_REC;
EXIT WHEN MR_SHIP_ADDR_SELECT_CURSOR%NOTFOUND;
INSERT INTO HP_HOMEPC_RT (SALE_GRP,AGENT_ID,SENDER_ID,HPID,PL,ADDRESS,MODIFY_TIME,MODIFY_NAME,CREATE_TIME)
VALUES (MR_SHIP_ADDR_REC.V_SALES_ID,DC_ERP_AGENT_ID,TEMP_SHIP_ID,'',MR_SHIP_ADDR_REC.V_PROD_GRP,TEMP_SHIP_ADDR,SYSDATE,'system',SYSDATE);
END LOOP;
END IF; END TRIGGER_MR_SHIP_ADDR_INSERT;
报错的信息是:29/47 PLS-00321: expression 'MR_SHIP_ADDR_REC' is inappropriate as the left hand side of an assignment statement
29/9 PL/SQL: SQL Statement ignored
32/77 PLS-00330: invalid use of type name or subtype name
32/77 PL/SQL: ORA-00904: "MR_SHIP_ADDR_REC"."V_PROD_GRP": invalid identifier
31/9 PL/SQL: SQL Statement ignored
不要定义MR_SHIP_ADDR_REC了,直接用两个变量好了。