oracle触发器的编写

触发器是一种特殊类型的存储过程，触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，ORACLE 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。
ORACLE中简单的触发器定义如下：
[b]1 对于表SFIS1.R_LINE_EXPENSE_TIME_T的delete和update操作时添加一条历史记录到备份表中[/b]
[quote]CREATE OR REPLACE TRIGGER SFIS1.BACKUP_LINE_EXPENSE_TIME（触发器名称）
AFTER DELETE OR UPDATE（触发事件）
ON SFIS1.R_LINE_EXPENSE_TIME_T FOR EACH ROW（触发的table）
BEGIN
INSERT INTO SFIS1.H_LINE_EXPENSE_TIME_T(
WORK_DATE ,
WORK_CLASS,
LINE_NAME ,
ERROR_CODE ,
DUTY_CODE ,
START_TIME,
REPAIR_TIME ,
RESTORE_TIME,
WARNING_TIME,
REPAIR_EMP,
REASON_DESC,
RESOLUTION_DESC ,
REAL_DUTY_CODE,
ANALYZE_EMP
)VALUES(
:OLD.WORK_DATE ,
:OLD.WORK_CLASS,
:OLD.LINE_NAME ,
:OLD.ERROR_CODE ,
:OLD.DUTY_CODE ,
:OLD.START_TIME,
:OLD.REPAIR_TIME ,
:OLD.RESTORE_TIME,
:OLD.WARNING_TIME,
:OLD.REPAIR_EMP,
:OLD.REASON_DESC,
:OLD.RESOLUTION_DESC ,
:OLD.REAL_DUTY_CODE,
:OLD.ANALYZE_EMP
);
END;[/quote]
[b]2 以下触发器用在一个签核系统中用户的工作区，当用户签核完成时根据表单状态的更改触发触发器更改工作区的标志位[/b]DROP TRIGGER [quote]CTVGRDDCC.CHANGE_COUNTERSIGN_WORKSPACE;

CREATE OR REPLACE TRIGGER CTVGRDDCC.CHANGE_COUNTERSIGN_WORKSPACE
AFTER UPDATE
ON CTVGRDDCC.R_SIGN_ORDER_T
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.C_STATUS='REWORK' OR :NEW.C_STATUS='REJECTED' OR :NEW.C_STATUS='DCCREJECTED' THEN
UPDATE R_SIGN_WORKSPACE_T SET FLAG='2',C_DATE=SYSDATE WHERE TABLE_NO=:OLD.TABLE_NO AND USER_ID=:OLD.C_USER_ID
AND C_DEP_NAME=:OLD.C_DEPT AND FLAG='0';
UPDATE R_SIGN_WORKSPACE_T SET FLAG='3',C_DATE=SYSDATE WHERE TABLE_NO=:OLD.TABLE_NO AND FLAG='0';--一個單有退件的其它的改成3
END IF;
IF :NEW.C_STATUS='Y' THEN
UPDATE R_SIGN_WORKSPACE_T SET FLAG='1',C_DATE=SYSDATE WHERE TABLE_NO=:OLD.TABLE_NO AND USER_ID=:OLD.C_USER_ID
AND C_DEP_NAME=:OLD.C_DEPT AND FLAG='0';
--相同單位只需要簽核一個主管,其它主管標志位置4
--UPDATE R_SIGN_WORKSPACE_T SET FLAG='4',C_DATE=SYSDATE WHERE TABLE_NO=:OLD.TABLE_NO AND C_DEP_NAME=:OLD.C_DEPT AND :OLD.C_DATE IS NULL;
END IF;
END CHANGE_COUNTERSIGN_WORKSPACE;
/[/quote][b]
3 这个例子是在sfc系统中更细工令产量的，这个例子更接近与存储过程[/b]
[quote]AFTER INSERT
ON SFIS1.C_WIP_MO_KEYPART_T
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
MO VARCHAR2(30);
PARTNO VARCHAR2(30);
MODELNAME VARCHAR2(30);
C_MODELNAME VARCHAR2(30);
PART_QTY NUMBER(6);
C_QTY NUMBER(6);
TEMP_COUNT NUMBER(4);
D_DATE DATE DEFAULT SYSDATE;
BEGIN
MO:=:NEW.MO_NUMBER;
PARTNO:=:NEW.MATNR;
PART_QTY:=:NEW.MENGE01;
C_QTY:=0;
SELECT COUNT(*) INTO TEMP_COUNT FROM SFIS1.C_WIP_T WHERE MO_NUMBER=MO AND KEY_PART_NO=PARTNO;
IF TEMP_COUNT=1 THEN
SELECT KEY_PART_QTY-KEY_PART_WIP_QTY INTO C_QTY FROM SFIS1.C_WIP_T WHERE MO_NUMBER=MO AND KEY_PART_NO=PARTNO;
UPDATE SFIS1.C_WIP_T SET KEY_PART_QTY=PART_QTY,KEY_PART_WIP_QTY=PART_QTY-C_QTY,UPDATE_TIME=D_DATE WHERE MO_NUMBER=MO AND KEY_PART_NO=PARTNO;
ELSE
BEGIN
SELECT MODEL_NAME INTO MODELNAME FROM SFISM4.R_MO_BASE_T WHERE MO_NUMBER=MO;
SELECT SKU_MODEL INTO C_MODELNAME FROM SFIS1.C_MODEL_DESC_T WHERE MODEL_NAME=MODELNAME;
EXCEPTION
WHEN OTHERS THEN
MODELNAME:='NO_MO_IN_SFC';
C_MODELNAME:='NO_MO_IN_SFC';
END;
INSERT INTO SFIS1.C_WIP_T(MODEL_NAME,MO_NUMBER,KEY_PART_NO,KEY_PART_QTY,KEY_PART_WIP_QTY,UPDATE_TIME,HH_MODEL)
VALUES(C_MODELNAME,MO,PARTNO,PART_QTY,PART_QTY,D_DATE,MODELNAME);
END IF;
END;[/quote]
以上三个例子是迄今为止我所写过的所有的触发器，能够满足我目前工作的需要了，可能有些地方不太合理，欢迎大家指正.