ORACLE的触发器TRIGGER的用法

文4880

　　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;