69 lines
2.7 KiB
Plaintext
69 lines
2.7 KiB
Plaintext
create or replace TRIGGER trg_uf_competency_evalu_dt1
|
||
BEFORE INSERT OR UPDATE ON uf_competency_evalu_dt1
|
||
FOR EACH ROW
|
||
BEGIN
|
||
-- kpdfzy (专业)
|
||
IF INSERTING OR
|
||
(:NEW.kpdfzy IS NULL AND :OLD.kpdfzy IS NOT NULL) OR -- 情况1：新值为空，时间设为空
|
||
(:OLD.kpdfzy IS NULL AND :NEW.kpdfzy IS NOT NULL) OR -- 情况2：旧值为空，新值有值，更新时间
|
||
(:OLD.kpdfzy IS NOT NULL AND :NEW.kpdfzy IS NOT NULL AND :OLD.kpdfzy <> :NEW.kpdfzy) THEN -- 情况3：都不为空且不相等，更新时间
|
||
|
||
IF :NEW.kpdfzy IS NOT NULL THEN
|
||
:NEW.professional_time := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
|
||
ELSE
|
||
:NEW.professional_time := NULL;
|
||
END IF;
|
||
END IF;
|
||
|
||
-- kpdfbm (部门)
|
||
IF INSERTING OR
|
||
(:NEW.kpdfbm IS NULL AND :OLD.kpdfbm IS NOT NULL) OR
|
||
(:OLD.kpdfbm IS NULL AND :NEW.kpdfbm IS NOT NULL) OR
|
||
(:OLD.kpdfbm IS NOT NULL AND :NEW.kpdfbm IS NOT NULL AND :OLD.kpdfbm <> :NEW.kpdfbm) THEN
|
||
|
||
IF :NEW.kpdfbm IS NOT NULL THEN
|
||
:NEW.department_time := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
|
||
ELSE
|
||
:NEW.department_time := NULL;
|
||
END IF;
|
||
END IF;
|
||
|
||
-- kpdfsyb (事业部)
|
||
IF INSERTING OR
|
||
(:NEW.kpdfsyb IS NULL AND :OLD.kpdfsyb IS NOT NULL) OR
|
||
(:OLD.kpdfsyb IS NULL AND :NEW.kpdfsyb IS NOT NULL) OR
|
||
(:OLD.kpdfsyb IS NOT NULL AND :NEW.kpdfsyb IS NOT NULL AND :OLD.kpdfsyb <> :NEW.kpdfsyb) THEN
|
||
|
||
IF :NEW.kpdfsyb IS NOT NULL THEN
|
||
:NEW.businessUnit_time := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
|
||
ELSE
|
||
:NEW.businessUnit_time := NULL;
|
||
END IF;
|
||
END IF;
|
||
|
||
-- kpdfgs (公司)
|
||
IF INSERTING OR
|
||
(:NEW.kpdfgs IS NULL AND :OLD.kpdfgs IS NOT NULL) OR
|
||
(:OLD.kpdfgs IS NULL AND :NEW.kpdfgs IS NOT NULL) OR
|
||
(:OLD.kpdfgs IS NOT NULL AND :NEW.kpdfgs IS NOT NULL AND :OLD.kpdfgs <> :NEW.kpdfgs) THEN
|
||
|
||
IF :NEW.kpdfgs IS NOT NULL THEN
|
||
:NEW.company_time := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
|
||
ELSE
|
||
:NEW.company_time := NULL;
|
||
END IF;
|
||
END IF;
|
||
|
||
-- examScore (考试分数)
|
||
IF INSERTING OR
|
||
(:NEW.examScore IS NULL AND :OLD.examScore IS NOT NULL) OR
|
||
(:OLD.examScore IS NULL AND :NEW.examScore IS NOT NULL) OR
|
||
(:OLD.examScore IS NOT NULL AND :NEW.examScore IS NOT NULL AND :OLD.examScore <> :NEW.examScore) THEN
|
||
|
||
IF :NEW.examScore IS NOT NULL THEN
|
||
:NEW.final_time := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
|
||
ELSE
|
||
:NEW.final_time := NULL;
|
||
END IF;
|
||
END IF;
|
||
END; |