Oracle中Before和After觸發器的例子

做一個表的insert的trigger，目的是只修改插入行的欄位。  

CREATE OR REPLACE TRIGGER TR_RME_SLOT
BEFORE  INSERT ON RME_SLOT
FOR EACH ROW
BEGIN
  IF (:NEW.POSITION >= 0 AND :NEW.POSITION <10) THEN
      :NEW.SLOT_NAME := '0'||TO_CHAR(:NEW.POSITION);
  ELSE
      :NEW.SLOT_NAME := TO_CHAR(:NEW.POSITION);
  END IF;
END;  

在插入以前就需要修改插入行；在trigger實現中並不需要用到update語句  

同時，如果要在trigger中實現對本表記錄的修改，則需要這樣寫：  

CREATE OR REPLACE TRIGGER TR_RME_SLOT
AFTER  INSERT ON RME_SLOT
FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF (:NEW.POSITION >= 0 AND :NEW.POSITION <10) THEN
      UPDATE RME_SLOT  SET SLOT_NAME = '0'||TO_CHAR(:NEW.POSITION) WHERE SLOT_ID=:NEW.SLOT_ID;
  ELSE
      UPDATE RME_SLOT  SET SLOT_NAME = TO_CHAR(:NEW.POSITION) WHERE SLOT_ID=:NEW.SLOT_ID;
  END IF;
  COMMIT;
END; 

注意多了一段DECLARE，同時在trigger結束時需要COMMIT

CREATE OR REPLACE TRIGGER tr_AfterUpdate
AFTER INSERT OR UPDATE
ON TEST_TRIGGER
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

   UPDATE TEST_TRIGGER
   SET T_FACT = T_SALARY*(1-T_TAX)-T_EAT-T_HOUSE
   WHERE T_NAME = :NEW.T_NAME;
   COMMIT;  

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END tr_testUpdate;