ORACLE的TRIGGER的用法

ORACLE的TRIGGER的用法　 http://www.8888r.com/blog/user/oracle/archives/2006/11045.html     看過這個篇文章之後,對自己的提高很大,我寫過很多存儲過程,很多函數,很多程式包,唯獨沒有寫過TRIGGER,其實自己對TRIGGER的了解還不是很深,一直想試試,可惜沒有機會.看過此文章後,覺得自己應該有能力寫一個非常标準的TRIGGER了.

    附原文:

    原文引自 日月明王

                           TRIGGER之我見

                                                   日月明王

                               http://spaces.msn.com/sunmoonking/

  1. trigger 是自動送出的，不用COMMIT，ROLLBACK

  2. trigger最大為32K，如果有複雜的應用可以通過在TRIGGER裡調用PROCEDURE或FUNCTION來實作。

  3. 文法CREATE OR REPLACE TRIGGER <trigger_name>

<BEFORE | AFTER> <ACTION>

ON <table_name>

DECLARE

 <variable definitions>

BEGIN

  <trigger_code>

EXCEPTION

  <exception clauses>

END <trigger_name>;

/

  4. 相關指令

    create trigger

    create any trigger

    administer database trigger

    alter any trigger

    drop any trigger

  5. 對列做觸發（of）（行的觸發是最常見的，不在這裡列出

             1  create or replace trigger tri_wwm

             2  before update of id on wwm2 for each row

             3  declare the_str VARCHAR2(40):='update on wwm2''s id column';

             4  begin

             5      dbms_output.put_line(the_str);

             6* end tri_wwm;

           SQL> /

           Trigger created.

           SQL> update wwm2 set id=3;

           update on wwm2's id column

           1 row updated.

      6. Referencing 别名

          測試資料

           SQL> select * from wwm2;

                   ID NAME

           ---------- ----------

                    1 wwm

                    2 china

          建測試用日志表

             1  create table wwm_log

             2  (o_id number(8),o_name varchar2(10),

             3  n_id number(8),n_name varchar2(10),

             4* op_by varchar2(20),op_date date)

           SQL> /

           Table created.

           建立 觸發器

             create or replace trigger tri_refer

            after update of id on wwm2 referencing new as new old as old for each row

            begin

                insert into wwm_log values (:old.id,:old.name,:new.id,:new.name,sysdate,user);

            end;

            /

            更新表以觸發事件

           SQL> update wwm2 set id=8 where id=2;

           update on wwm2's id column

           1 row updated.

           SQL> select * from wwm_log;

                 O_ID O_NAME           N_ID N_NAME     OP_DATE   OP_USER

           ---------- ---------- ---------- ---------- --------- -----------------

                    2 china               8 china      09-MAR-06 SYSTEM

                 大家可以用這個方法來對一些操作做日志

      7.Disable/Enable

         ALTER  TRIGGER  tri_refer DISABLE/ENABLE

         ALTER  TABLE wwm2 DISABLE/ENABLE ALL TRIGGERS;

         ALTER TRIGGER tri_refer RENAME TO tri_reference;

      TRIGGER的種類還有很多，如DDL和SYSTEM的觸發器，下次用到時再寫吧。