trigger

--dml trigger
-------------------------------------------------------------------------
SQL> create or replace trigger tr_magy01
  2  before
  3  insert on t1
  4  begin
  5  insert into t2 values(101,101,'trigger test 01');
  6  end;
  7  /

Trigger created.

SQL> insert into t1 values(10000000000,1,'cccccccccc');

1 row created.

SQL> select * from t2 where a=101;

         A          B C
---------- ---------- --------------------
       101        101 trigger test 01

SQL> rollback;

Rollback complete.

SQL> select * from t2 where a=101;

no rows selected

SQL> begin        
  2  for i in 1..10 loop
  3  insert into t1 values(1000000000+i,i,'trigger test 01');
  4  end loop
  5  ;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from t2 where a=101;

         A          B C
---------- ---------- --------------------
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01
       101        101 trigger test 01

10 rows selected.

SQL> truncate table t1;

Table truncated.

SQL> delete from t2 where a=101;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t2 where rownum<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select * from t2 where a=101;

         A          B C
---------- ---------- --------------------
       101        101 trigger test 01

SQL>
测试结果：无论提交与否，执行一次insert就会触发一次trigger，包括一次insert commit，十次insert一个commit，一次insert select多行。

-------------------------------------------------------------------------
--ddl trigger
-------------------------------------------------------------------------

SQL>
SQL> create or replace trigger tr_magy02
  2  after
  3  create on schema
  4  begin
  5  insert into t2 values(102,102,'trigger ddl test');
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> create table t3 as select * from t2 where 1=2;

Table created.

SQL> select * from t2 where a=102;

         A          B C
---------- ---------- --------------------
       102        102 trigger ddl test

SQL>

-------------------------------------------------------------------------
--database trigger
-------------------------------------------------------------------------
create or replace trigger tr_magy03
after logon on database
begin
insert into t2 values(103,103,'database trigger');
end;
/

 

 

 

 

 

 

 

 

 

 

 

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

转载于:http://blog.itpub.net/23382569/viewspace-761080/