ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE的TRIGGER的用法 

ORACLE的TRIGGER的用法 

原创 Linux操作系统 作者:aikangyan 时间:2008-01-07 10:06:38 0 删除 编辑
1. trigger 是自动提交的,不用COMMIT,ROLLBACK

  2. trigger最大为32K,如果有复杂的应用可以通过在TRIGGER里调用PROCEDURE或FUNCTION来实现。

  3. 语法CREATE OR REPLACE TRIGGER

ON

DECLARE
 
BEGIN
 
EXCEPTION
 
END ;
/

  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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12379366/viewspace-111390/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    81
  • 访问量
    42950