EBS: 触发器三种状态（INSERTING, UPDATING , DELETING ）实例

 

ORACLE PLSQL  触发器三种状态（INSERTING, UPDATING , DELETING ） 实例应用 

将实际领用物料数量 回写 需求明细表 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

/* 增加 “实际发料数量 ” ALTER TABLE CUX.CUX_PROJECT_REQUIREMENT_TB ADD ACTUAL_QUANTITY NUMBER;   COMMENT ON COLUMN CUX.CUX_PROJECT_REQUIREMENT_TB.ACTUAL_QUANTITY IS '实际领用数量'; */     create or replace trigger CUX_WIP_TRX_LINES_ALL_TR   after insert or update or delete   on CUX_wip_trx_lines_all   REFERENCING OLD AS OLD NEW AS NEW   FOR EACH ROW     declare   -- local variables here begin    IF UPDATING THEN    IF (:NEW.ATTRIBUTE4 IS NOT NULL) AND (:NEW.ACTUAL_QUANTITY<> :OLD.ACTUAL_QUANTITY) THEN      UPDATE CUX.CUX_PROJECT_REQUIREMENT_TB           SET ACTUAL_QUANTITY = --GET_ACTUAL_QUANTITY(:NEW.ATTRIBUTE4),                   NVL(ACTUAL_QUANTITY,0) + NVL(:NEW.ACTUAL_QUANTITY,0) - NVL(:OLD.ACTUAL_QUANTITY,0)，               LAST_UPDATE_DATE = SYSDATE,               LAST_UPDATED_BY = FND_GLOBAL.USER_ID           WHERE LINE_ID = :NEW.ATTRIBUTE4 ;          END IF ;    ELSIF INSERTING THEN      IF (:NEW.ATTRIBUTE4 IS NOT NULL) THEN      UPDATE CUX.CUX_PROJECT_REQUIREMENT_TB           SET ACTUAL_QUANTITY = NVL(ACTUAL_QUANTITY,0) + NVL(:NEW.ACTUAL_QUANTITY,0)，  -- GET_ACTUAL_QUANTITY(:NEW.ATTRIBUTE4),               LAST_UPDATE_DATE = SYSDATE,               LAST_UPDATED_BY = FND_GLOBAL.USER_ID           WHERE LINE_ID = :NEW.ATTRIBUTE4 ;        END IF;          ELSIF DELETING THEN       IF (:OLD.ATTRIBUTE4 IS NOT NULL) THEN        UPDATE CUX.CUX_PROJECT_REQUIREMENT_TB           SET ACTUAL_QUANTITY =  NVL(ACTUAL_QUANTITY,0) - NVL(:OLD.ACTUAL_QUANTITY,0)，  -- GET_ACTUAL_QUANTITY(:NEW.ATTRIBUTE4),               LAST_UPDATE_DATE = SYSDATE,               LAST_UPDATED_BY = FND_GLOBAL.USER_ID           WHERE LINE_ID = :OLD.ATTRIBUTE4 ;       END IF;        END IF;    -- COMMIT;              end CUX_WIP_TRX_LINES_ALL_TR;

　　

 

以上