About Oracle Create Trigger(SQL)_oracle crerate trigger-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45883933/article/details/105048696

本文详细介绍了在Oracle数据库中创建触发器的过程，特别关注于在特定条件下插入数据到历史记录表的触发器实现。该触发器在WIP_COMPONENTUNIT_HIS表上操作，当TRANS_TYPE为特定值时，将数据插入到TRANS_COMPONENT_QMS_HIS表，确保了数据的一致性和历史跟踪。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle Create Trigger(SQL)

CREATE OR REPLACE TRIGGER 
TR_TRANS_COMPONENT_QMS_HIS
  AFTER INSERT ON WIP_COMPONENTUNIT_HIS
  FOR EACH ROW

WHEN(NEW.TRANS_TYPE IN ('TRACKOUT','ASSEMBLY')
     OR (NEW.TRANS_TYPE = 'MOVENEXT' AND NEW.LAST_STEP_NAME = '19999' AND NEW.STEP_NAME IS NULL)
)
DECLARE
V_STEP_NAME WIP_COMPONENTUNIT_HIS.STEP_NAME%TYPE;
BEGIN
  IF :NEW.TRANS_TYPE='MOVENEXT' THEN
    V_STEP_NAME:='19999';
  ELSE
     V_STEP_NAME:=:NEW.STEP_NAME;
  END IF;
  INSERT INTO TRANS_COMPONENT_QMS_HIS
    (COMPONENT_RRN,
     ORG_RRN,
     UPDATED_BY,
     TRANS_TYPE,
     TRANS_TIME,
     HISTORY_SEQ,
     HISTORY_SEQ_NO,
     COMPONENT_ID,
     COMPONENT_TYPE,
     WO_ID,
     SUBSTRATE_ID1,
     PART_NAME,
     PART_VERSION,
     PART_DESC,
     PART_TYPE,
     LAST_PART_NAME,
     MAIN_MAT_TYPE,
     SUB_MAT_TYPE,
     CUSTOMER_CODE,
     CUSTOMER_ORDER,
     CUSTOMER_PART_ID,
     CUSTOMER_LOT_ID,
     PRIORITY,
     PLAN_START_DATE,
     PLAN_END_DATE,
     REQUIRE_DATE,
     GRADE1,
     GRADE2,
     JUDGE1,
     JUDGE2,
     REWORK_CODE,
     WAREHOUSE_ID,
     LOCATOR_ID,
     LOCATION,
     LINE_ID,
     STAGE_ID,
     DURABLE,
     POSITION,
     OWNER,
     LOT_COMMENT,
     MAIN_QTY,
     SUB_QTY,
     EQUIPMENT_ID,
     LAST_EQUIPMENT_ID,
     QUEUE_TIME,
     TRACK_IN_TIME,
     TRACK_OUT_MAIN_QTY,
     SUB_UNIT_TYPE,
     COM_CLASS,
     STATE,
     SUB_STATE,
     HOLD_STATE,
     TRANSFER_STATE,
     STATE_ENTRY_TIME,
     PRE_TRANS_TYPE,
     PRE_COM_CLASS,
     PRE_STATE,
     PRE_SUB_STATE,
     CURRENT_SEQ,
     PROCESS_INSTANCE_RRN,
     PROCESS_RRN,
     PROCESS_NAME,
     PROCESS_VERSION,
     PROCEDURE_RRN,
     PROCEDURE_NAME,
     PROCEDURE_VERSION,
     STEP_RRN,
     STEP_NAME,
     STEP_VERSION,
     STEP_DESC,
     LAST_STEP_NAME,
     BATCH_ID,
     REWORK_STACK_COUNT,
     REWORK_COUNT,
     RECIPE_NAME,
     RECIPE_VERSION,
     MASK,
     ACTION_CODE,
     ACTION_REASON,
     ACTION_COMMENT,
     HIS_COMMENT,
     TRANS_SUB_QTY,
     TRANS_MAIN_QTY,
     TRACK_OUT_TIME,
     MANUFACTURE_TYPE,
     LAST_MAIN_LOT_ID,
     EQUIPMENT_TYPE)
  VALUES
    (:NEW.COMPONENT_RRN,
     :NEW.ORG_RRN,
     :NEW.UPDATED_BY,
     :NEW.TRANS_TYPE,
     :NEW.TRANS_TIME,
     :NEW.HISTORY_SEQ,
     :NEW.HISTORY_SEQ_NO,
     :NEW.COMPONENT_ID,
     :NEW.COMPONENT_TYPE,
     :NEW.WO_ID,
     :NEW.SUBSTRATE_ID1,
     :NEW.PART_NAME,
     :NEW.PART_VERSION,
     :NEW.PART_DESC,
     :NEW.PART_TYPE,
     :NEW.LAST_PART_NAME,
     :NEW.MAIN_MAT_TYPE,
     :NEW.SUB_MAT_TYPE,
     :NEW.CUSTOMER_CODE,
     :NEW.CUSTOMER_ORDER,
     :NEW.CUSTOMER_PART_ID,
     :NEW.CUSTOMER_LOT_ID,
     :NEW.PRIORITY,
     TO_CHAR(:NEW.PLAN_START_DATE,'yyyymmddhh24miss'),
     TO_CHAR(:NEW.PLAN_END_DATE,'yyyymmddhh24miss'),
     TO_CHAR(:NEW.REQUIRE_DATE,'yyyymmddhh24miss'),
     :NEW.GRADE1,
     :NEW.GRADE2,
     :NEW.JUDGE1,
     :NEW.JUDGE2,
     :NEW.REWORK_CODE,
     :NEW.WAREHOUSE_ID,
     :NEW.LOCATOR_ID,
     :NEW.LOCATION,
     :NEW.LINE_ID,
     :NEW.STAGE_ID,
     :NEW.DURABLE,
     :NEW.POSITION,
     :NEW.OWNER,
     :NEW.LOT_COMMENT,
     :NEW.MAIN_QTY,
     :NEW.SUB_QTY,
     :NEW.EQUIPMENT_ID,
     :NEW.LAST_EQUIPMENT_ID,
     TO_CHAR(:NEW.QUEUE_TIME,'yyyymmddhh24miss'),
     TO_CHAR(:NEW.TRACK_IN_TIME,'yyyymmddhh24miss'),
     :NEW.TRACK_OUT_MAIN_QTY,
     :NEW.SUB_UNIT_TYPE,
     :NEW.COM_CLASS,
     :NEW.STATE,
     :NEW.SUB_STATE,
     :NEW.HOLD_STATE,
     :NEW.TRANSFER_STATE,
     TO_CHAR(:NEW.STATE_ENTRY_TIME,'yyyymmddhh24miss'),
     :NEW.PRE_TRANS_TYPE,
     :NEW.PRE_COM_CLASS,
     :NEW.PRE_STATE,
     :NEW.PRE_SUB_STATE,
     :NEW.CURRENT_SEQ,
     :NEW.PROCESS_INSTANCE_RRN,
     :NEW.PROCESS_RRN,
     :NEW.PROCESS_NAME,
     :NEW.PROCESS_VERSION,
     :NEW.PROCEDURE_RRN,
     :NEW.PROCEDURE_NAME,
     :NEW.PROCEDURE_VERSION,
     :NEW.STEP_RRN,
     V_STEP_NAME,
     :NEW.STEP_VERSION,
     :NEW.STEP_DESC,
     :NEW.LAST_STEP_NAME,
     :NEW.BATCH_ID,
     :NEW.REWORK_STACK_COUNT,
     :NEW.REWORK_COUNT,
     :NEW.RECIPE_NAME,
     :NEW.RECIPE_VERSION,
     :NEW.MASK,
     :NEW.ACTION_CODE,
     :NEW.ACTION_REASON,
     :NEW.ACTION_COMMENT,
     :NEW.HIS_COMMENT,
     :NEW.TRANS_SUB_QTY,
     :NEW.TRANS_MAIN_QTY,
     TO_CHAR(:NEW.TRACK_OUT_TIME,'yyyymmddhh24miss'),
     :NEW.MANUFACTURE_TYPE,
     :NEW.LAST_MAIN_LOT_ID,
     'Equipment');
END TR_TRANS_COMPONENT_QMS_HIS;