做人事系統時對加班數據處理寫的一個觸發器-CSDN博客

本文链接：https://blog.csdn.net/linxi27/article/details/1487727

本文介绍了一个用于Oracle数据库的触发器，该触发器在T_OVERTIME表上进行操作，并更新T_SURPLUS表中的剩余小时数。触发器考虑了插入、更新和删除操作，并根据不同条件更新剩余小时数。

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

CREATE OR REPLACE TRIGGER DBAUSER.UPD_SURPLUS

AFTER DELETE OR INSERT OR UPDATE

ON DBAUSER.T_OVERTIME

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

countsum1 NUMBER;

countsum2 NUMBER;

countsum3 NUMBER;

countsum4 NUMBER;

countsum5 NUMBER;

countsum6 NUMBER;

/******************************************************************************

NAME: TUpdate_TSURPLUS

PURPOSE:

REVISIONS:

Ver Date Author Description

--------- ---------- --------------- ------------------------------------

1.0 2007/01/13 zhoutao. Created this trigger.

NOTES:

Automatically available Auto Replace Keywords:

Object Name: TUpdate_TSURPLUS

Sysdate: 2007/01/13

Date and Time: 2007/01/13, AM 08:48:40, and 2007/01/13 AM 08:48:40

Username: (set in TOAD Options, Proc Templates)

Table Name: T_OVERTIME (set in the "New PL/SQL Object" dialog)

Trigger Options: (set in the "New PL/SQL Object" dialog)

******************************************************************************/

BEGIN

countsum1:=0;

countsum2:=0;

countsum3:=0;

countsum4:=0;

countsum5:=0;

countsum6:=0;

IF INSERTING THEN

SELECT COUNT(*) INTO countsum1 FROM T_SURPLUS WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT ;

IF countsum1>0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

ELSIF countsum1=0 THEN

INSERT INTO T_SURPLUS(IDS,DATES,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,TO_CHAR(:NEW.PRTDATE,'MM/YYYY'),:NEW.VRTMSORT,:NEW.AHR);

END IF;

ELSIF UPDATING THEN

--&&&&&*********&&&&&&&&&------

IF :NEW.VRTMSORT=:OLD.VRTMSORT THEN

IF :NEW.AHR<>:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')<>TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

--矪耞囊э跑い毳骰Pセるぃ琌る计э跑暗猭

SELECT COUNT(*) INTO countsum2 FROM T_SURPLUS WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT ;

IF countsum2>0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

ELSIF countsum2=0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

INSERT INTO T_SURPLUS(IDS,DATES,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,TO_CHAR(:NEW.PRTDATE,'MM/YYYY'),:NEW.VRTMSORT,:NEW.AHR);

END IF;

-----惫D挡

ELSIF :NEW.AHR=:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')<>TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

SELECT COUNT(*) INTO countsum3 FROM T_SURPLUS WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT ;

IF countsum3>0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

ELSIF countsum3=0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

INSERT INTO T_SURPLUS(IDS,DATES,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,TO_CHAR(:NEW.PRTDATE,'MM/YYYY'),:NEW.VRTMSORT,:NEW.AHR);

END IF;

ELSIF :NEW.AHR<>:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+(:NEW.AHR-:OLD.AHR) WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

END IF;

--&&&&&*********&&&&&&&&&------

ELSIF :NEW.VRTMSORT<>:OLD.VRTMSORT THEN

----&&&&&&&&&&&&&&&*****************&&&&&&&&&&&&&&&&&----

IF :NEW.AHR<>:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')<>TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

SELECT COUNT(*) INTO countsum4 FROM T_SURPLUS WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT ;

IF countsum4>0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

ELSIF countsum4=0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

INSERT INTO T_SURPLUS(IDS,DATES,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,TO_CHAR(:NEW.PRTDATE,'MM/YYYY'),:NEW.VRTMSORT,:NEW.AHR);

END IF;

----&&&&&&&&&&&&&&&*****************&&&&&&&&&&&&&&&&&----

ELSIF :NEW.AHR=:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')<>TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

SELECT COUNT(*) INTO countsum5 FROM T_SURPLUS WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT ;

IF countsum5>0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

ELSIF countsum5=0 THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

INSERT INTO T_SURPLUS(IDS,DATES,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,TO_CHAR(:NEW.PRTDATE,'MM/YYYY'),:NEW.VRTMSORT,:NEW.AHR);

END IF;

ELSIF :NEW.AHR<>:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:OLD.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

ELSIF :NEW.AHR=:OLD.AHR AND TO_CHAR(:NEW.PRTDATE,'MM/YYYY')=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR+:NEW.AHR WHERE T_SURPLUS.IDS=:NEW.IDS AND T_SURPLUS.DATES=TO_CHAR(:NEW.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:NEW.VRTMSORT;

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:OLD.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

END IF;

ELSIF DELETING THEN

UPDATE T_SURPLUS SET T_SURPLUS.SRPLS_HR=T_SURPLUS.SRPLS_HR-:OLD.AHR WHERE T_SURPLUS.IDS=:OLD.IDS AND T_SURPLUS.DATES=TO_CHAR(:OLD.PRTDATE,'MM/YYYY') AND T_SURPLUS.VRTM_SORT=:OLD.VRTMSORT;

END IF;

EXCEPTION

WHEN OTHERS THEN

-- Consider logging the error and then re-raise

RAISE;

END TUpdate_TSURPLUS;