做人事系統時對加班數據處理寫的一個觸發器
来源:互联网 发布:广电网络还没整合 编辑:程序博客网 时间:2024/04/30 07:24
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;
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;
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;
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;
- 做人事系統時對加班數據處理寫的一個觸發器
- 急需一份ASP+ACCESS做的人事关系系统
- 加班,无聊的加班
- 程序员误区:做软件开发要加班熬夜的工作
- 程序员误区:做软件开发要加班熬夜的工作
- 做IT想不加班真的很难
- 做IT想不加班真的很难。
- 作为开发者,你天天加班,做的累不累?
- 工程的人事物
- 人事
- 人事工资管理系统的开发
- 人事信息管理系统的源代码
- 《高效能人事的七个习惯》
- 通达OA二次开发 小飞鱼智能人事加班请假流程开发(图文)
- 关于加班这件事吐槽两句-加班的种类划分
- 今天加班到现在,吃了个面包,有点无聊,讨厌做这种客户的软件。
- 明天两个弟弟来我家,今天晚上加加班,把明天要做的事先做掉点
- 程序员误区2:做软件开发必须要加班熬夜的工作
- SMS 2003安装配置系列之二:SMS 2003主站点配置指南(1)--配置站点属性及站点系统
- JavaScript实现图片的放大、缩小、平移
- Pro visual c++/cli and .net 2.0 platform2 学习笔记(9第三章 面向对象的C++/CLI==2)
- 2007/01/19 星期五 晴
- SMS 2003安装配置系列之三:SMS 2003主站点配置指南(2)--配置资源发现方式及客户端的安装
- 做人事系統時對加班數據處理寫的一個觸發器
- uboot移植文档
- DOS批处理脚本语言简介
- 你凭什么创业?
- 纯虚函数
- SMS 2003操作指南系列之一:SMS2003软硬件清单收集配置指南
- 互斥对象(转)
- SMS 2003操作指南系列之二:SMS 2003软件分发指南(配置篇)
- 如何在struts+spring+hibernate的框架下构建低耦合高内聚的软件