【oracle】觸發器簡單實現
目標：實現實時備份uertest表數據至usertest_temp中，兩表結構一致 解決：用oracle觸發器實現同步 結果： 1.建表 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 [sql] -- 簡單的用戶表 create table USERTEST ( NAME VARCHAR2(20) not null, AGE NUMBER, ISDELETE VARCHAR2(4) ) -- 備份表 create table USERTEST_TEMP ( NAME VARCHAR2(20) not null, AGE NUMBER, ISDELETE VARCHAR2(4) )2、觸發器 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 [sql] CREATE OR REPLACE TRIGGER tr_user_temp BEFORE INSERT OR UPDATE OR DELETE ON usertest FOR EACH ROW declare BEGIN IF inserting THEN INSERT INTO usertest_temp(name,age,isdelete) VALUES ( :new.name, :new.age, :new.isdelete ); ELSIF deleting THEN DELETE usertest_temp WHERE NAME =:OLD.NAME ; ELSIF updating THEN update usertest_temp SET age =:new.age, isdelete =:new.isdelete WHERE NAME =:OLD.NAME ; END IF ; END ;
3.對USERTEST表執行增刪改查操作，則usertest_temp表也會相應更改 注意：標點符號一定要是英文標點，否則會有編譯錯誤 理解 概念： 觸發器（trigger）是個特殊的存儲過程，它的執行是由事件來觸發自動地隱式運行[比如當對一個表進行操作（ insert，delete，update）時就會激活它執行]。觸發器經常用於加強數據的完整性約束和業務規則等。 觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 數據字典中查到。觸發器與存儲過程的唯一區別是觸發器不能執行EXECUTE語句調用，而是在用戶執行Transact-SQL語句時自動觸發執行。 創建語法： ? 1 2 3 4 5 6 7 8 9 10 [sql] CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } {INSERT | DELETE | UPDATE [OF column [, column …]]} [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON [schema.]table_name | [schema.]view_name [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] [WHEN condition] PL/SQL_BLOCK | CALL procedure_name;
注釋： CREATE [OR REPLACE] TRIGGER：創建或替換 BEFORE 和AFTER：觸發時序分別為前觸發【在執行觸發事件之前觸發當前所創建的觸發器】和後觸發方式【在執行觸發事件之後觸發當前所創建的觸發器】 FOR EACH ROW：行觸發器。 行觸發器：受到影響的每個數據行，只要它們符合觸發約束條件，均激活一次觸發器； 語句觸發器：將整個語句操作作為觸發事件，當它符合約束條件時，激活一次觸發器。 當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則只能為行觸發器。 REFERENCING：說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照當前的新、舊列值，默認的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。 WHEN 子句說明觸發約束條件。 Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。 WHEN 子句指定的觸發約束條件只能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。 注意以下幾點： 1.觸發器不接受參數。 2.一個表上最多可有12個觸發器，但同時、同事、同類的觸發器只能有一個且不可矛盾。 3.在一個表上的觸發器越多，對在該表上的DML操作的性能影響就越大。 4. 觸發器最大為32KB。若確實需要，可以先建立過程，然後在觸發器中用CALL語句進行調用。 5. 在觸發器的執行部分只能用DML語句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語句（CREATE、ALTER、DROP）。 6. 觸發器中不能包含事務控制語句(COMMIT，ROLLBACK，SAVEPOINT)。 7.在觸發器主體中調用的任何過程、函數，都不能使用事務控制語句。 8. 在觸發器主體中不能申明任何Long和blob變量。新值new和舊值old也不能向表中的任何long和blob列。 9.不同類型的觸發器(如DML觸發器、INSTEAD OF觸發器、系統觸發器)的語法格式和作用有較大區別。 問題：當觸發器被觸發時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、 後列的值. 實現: :NEW 修飾符訪問操作完成後列的值 :OLD 修飾符訪問操作完成前列的值 特性 INSERT UPDATE DELETE OLD NULL 實際值 實際值 NEW 實際值 實際值 NULL
