Trigger (Oracle)

出自 ProgWiki
前往: 導覽搜尋

相關資料

範例

CREATE OR REPLACE TRIGGER myuser.table_b_by_update
 BEFORE 
 UPDATE ON table_b REFERENCING NEW AS NEW OLD AS old FOR EACH ROW
 --使用變數
DECLARE
    v_ver table_a.ver%TYPE;
    v_id table_a.id%TYPE; 
BEGIN
    IF ((:old.data1<>:NEW.data1) OR
       (:old.remark<>:NEW.remark)
       ) THEN
 
            --取出table_a的id
            SELECT id INTO v_id
                FROM table_a
                WHERE no=:NEW.no;
 
            --取出table_a的版號,並把阪號遞增0.1,再轉為字串
            SELECT TRIM(TO_CHAR(TO_NUMBER(ver)+ 0.1, '9990.9'))
                INTO v_vaer
                FROM table_a WHERE id=v_id;
 
            --將新的版號寫回table_a
            UPDATE table_a
                SET ver=v_ver
                ,changedate=SYSDATE
                WHERE id=v_id;
 
            --備份舊版資料
            --(還沒)
       END IF;
END;