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

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

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

/* 增加 “实际发料数量 ”
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;

　　

以上