触发器

(一)DDL触发器
  Create Or Replace Trigger trigger_name 
  Before | After
  [Insert] | [ Or Update [Of 指定列] ] | [ Or Delete]
  On table_name 
  [ 
    For Each Row  --行级触发器 否则为表级触发器
      [When(old.column_name | new.column_name)] --限制行触发器
  ]
  PLSQL Block;
1、表级触发器
SQL> Create Or Replace Trigger trig1
  2  Before
  3  Insert Or Update Or Delete
  4  On tmpdept
  5  Declare
  6
  7  Begin
  8    Case
  9      When inserting Then
 10        dbms_output.put_line('插入');
 11      When updating Then
 12        dbms_output.put_line('修改');
 13      Else
 14        dbms_output.put_line('删除');
 15    End Case;
 16  Exception
 17    When Others Then
 18      raise_application_Error(-20000,'触发器错误！');
 19  End;
 20  /


触发器已创建


SQL> set serverout on
SQL> Update tmpdept Set deptno=deptno+1;
修改


已更新4行。


2、行级触发器
SQL> Create Or Replace Trigger trig1
  2  Before
  3  Insert Or Update Or Delete
  4  On tmpdept
  5  For Each Row
  6  Declare
  7
  8  Begin
  9    Case
 10      When inserting Then
 11        dbms_output.put_line('插入');
 12      When updating Then
 13        dbms_output.put_line('修改');
 14      Else
 15        dbms_output.put_line('删除');
 16    End Case;
 17  Exception
 18    When Others Then
 19      raise_application_Error(-20000,'触发器错误！');
 20  End;
 21
 22  /


触发器已创建


SQL> Update tmpdept Set deptno=deptno+1;
修改
修改
修改
修改


已更新4行。


SQL> rollback;


回退已完成。


SQL>


3、谓词触发器
SQL> Create Or Replace Trigger trig1
  2  Before
  3  Update Of deptno,dname
  4  On tmpdept
  5  For Each Row
  6  Declare
  7
  8  Begin
  9    Case
 10      When inserting Then
 11        dbms_output.put_line('插入');
 12      When updating Then
 13        dbms_output.put_line('修改');
 14      Else
 15        dbms_output.put_line('删除');
 16    End Case;
 17  Exception
 18    When Others Then
 19      raise_application_Error(-20000,'触发器错误！');
 20  End;
 21  /


触发器已创建


SQL> update tmpdept set deptno=deptno+1 where deptno=10;
修改


已更新 1 行。


SQL> update tmpdept set dname=dname||'a' where deptno=11;
修改


已更新 1 行。


SQL> update tmpdept set dname=substr(dname,1,length(dname)-1),loc=loc||'a' where
 deptno=11;
修改


4、新值、旧值
实例1：
SQL> Create Or Replace Trigger  trig2
  2  After
  3  Insert Or Update Or Delete
  4  On tmpdept
  5  For Each Row
  6  Begin
  7    Case
  8      When inserting Then
  9        dbms_output.put_line('新值 deptno='||:new.deptno||' dname='||:new.dna
me||' loc='||:new.loc);
 10      When updating Then
 11        dbms_output.put_line('新值 deptno='||:new.deptno||' dname='||:new.dna
me||' loc='||:new.loc||' 旧值 deptno='||:old.deptno||' dname='||:old.dname||' lo
c='||:old.loc);
 12      Else
 13        dbms_output.put_line('旧值 deptno='||:old.deptno||' dname='||:old.dna
me||' loc='||:old.loc);
 14    End Case;
 15  End;
 16  /


触发器已创建


SQL> insert into tmpdept values(50,'beijing','china');
新值 deptno=50 dname=beijing loc=china


已创建 1 行。


SQL> update tmpdept set dname='zhongguanchun' where deptno=50;
新值 deptno=50 dname=zhongguanchun loc=china 旧值 deptno=50 dname=beijing
loc=china


已更新 1 行。


SQL> delete tmpdept where deptno=50;
旧值 deptno=50 dname=zhongguanchun loc=china


已删除 1 行。


实例2：
SQL> CREATE OR REPLACE Trigger trig3
  2  Before
  3  Insert
  4  On tmpdept
  5  For Each Row
  6  Begin
  7     If :new.deptno<0 Then
  8        :new.deptno:=abs(:new.deptno);
  9     End If;
 10  End;
 11  /


触发器已创建


SQL> insert into tmpdept values(-60,'sh','china');


已创建 1 行。


SQL> select * from tmpdept where deptno=60;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        60 sh             china


SQL>


--:new的值在before状态下可更新


实例3：限制行触发器
SQL> CREATE OR REPLACE Trigger trig3
  2  Before
  3  Insert Or Update Or Delete
  4  On tmpdept
  5  For Each Row
  6    When (new.loc='sh')
  7  Begin
  8      dbms_output.put_line('新值 deptno='||:new.deptno||' dname='||:new.dname
||' loc='||:new.loc||' 旧值 deptno='||:old.deptno||' dname='||:old.dname||' loc=
'||:old.loc);
  9  End;
 10  /


触发器已创建


SQL> insert into tmpdept values(11,'a1','sh');
新值 deptno=11 dname=a1 loc=sh 旧值 deptno= dname= loc=


已创建 1 行。


SQL> insert into tmpdept values(12,'a2','SH');


已创建 1 行。


SQL> update tmpdept set loc='sh' where deptno=12;
新值 deptno=12 dname=a2 loc=sh 旧值 deptno=12 dname=a2 loc=SH


已更新 1 行。


SQL> delete tmpdept where deptno=12;


已删除 1 行。






(二)Instead Of 触发器
  Create Or Replace Trigger trigger_name 
  Instead Of 
  [Insert] | [ Or Update [Of 指定列] ] | [ Or Delete]
  On view_name 
  [ 
    For Each Row  --行级触发器 否则为表级触发器
      [When(old.column_name | new.column_name)] --限制行触发器
  ]
  PLSQL Block;


SQL> Create Table Student(SNO Number,SNAME Varchar2(10),Constraint Student_PK_SN
O Primary Key(SNO));


表已创建。


SQL> Insert Into Student Values(1,'张三');


已创建 1 行。


SQL> Insert Into Student Values(2,'李四');


已创建 1 行。


SQL> Insert Into Student Values(3,'王五');


已创建 1 行。


SQL> Commit;


提交完成。


SQL> Create Table Address(SNO Number References Student(SNO) On Delete Cascade ,
Addr Varchar2(50) );


表已创建。


SQL> Insert Into Address Values(1,'北京');


已创建 1 行。


SQL> Insert Into Address Values(2,'上海');


已创建 1 行。


SQL> Insert Into Address Values(3,'广州');


已创建 1 行。


SQL> Commit;


提交完成。
SQL> Create Or Replace Trigger trig1
  2  Instead Of
  3  Update
  4  On v_Student_Address
  5  Begin
  6   Delete Address Where  sno=:old.sno;
  7   Insert Into address Values(:new.sno,:new.addr);
  8  End;
  9  /


触发器已创建


SQL> update v_student_address set addr='sz' where sno=3;


已更新 1 行。


SQL> commit;


提交完成。


SQL> Create Or Replace Force View v_Student_Address As
  2  Select a.sno,a.sname,b.addr From Student a,Address b
  3  Where a.sno=b.sno
  4  /


视图已创建。


SQL> Create Or Replace Trigger trig1
  2  Instead Of
  3  Update
  4  On v_Student_Address
  5  For Each Row
  6  Begin
  7   Delete Address Where  sno=:old.sno;
  8   Insert Into address Values(:new.sno,:new.addr);
  9  End;
 10  /


触发器已创建


SQL> Update v_student_address Set addr='香港' Where sno=3;


已更新 1 行。


SQL> commit;


提交完成。


SQL> select * from v_student_address;


       SNO SNAME      ADDR
---------- ---------- --------------------------------------------------
         1 张三       北京
         2 李四       上海
         3 王五       香港


SQL>




(三) 数据库级触发器
Create Or Replace Trigger trigger_name
Before | After
Startup | Shutdown | Logon | LogOff
On Database
PLSQL block;


SQL> Create Table Event_Table(Event_Name Varchar2(100),dt Date);


表已创建。
SQL> Create Or Replace Trigger trig_shutdown
  2  Before
  3  Shutdown
  4  On Database
  5  Begin
  6    Insert Into event_table Values(ora_sysevent,Sysdate);
  7  End;
  8  /


触发器已创建


SQL> Create Or Replace Trigger trig_startup
  2  After
  3  startup
  4  On Database
  5  Begin
  6    Insert Into event_table Values(ora_sysevent,Sysdate);
  7  End;
  8  /


触发器已创建


SQL>


C:\Users\Administrator>net stop oracleserviceorcl
OracleServiceORCL 服务正在停止.
OracleServiceORCL 服务已成功停止。




C:\Users\Administrator>net start oracleserviceorcl
OracleServiceORCL 服务正在启动 .......
OracleServiceORCL 服务已经启动成功。


SQL> Select * From  Event_Table;


EVENT_NAME
-------------------------------------------------------------------------------


DT
--------------
SHUTDOWN
22-7月 -15


STARTUP
22-7月 -15






SQL> Create Table log_table(log_user Varchar2(20),logon Date,logoff Date,ip_addr
ess Varchar2(20));


表已创建。


SQL> Create Or Replace Trigger trig_logon
  2  After
  3  logon
  4  On Database
  5  Begin
  6    Insert Into log_table Values(ora_login_user,Sysdate,Null,ora_client_ip_ad
dress);
  7  End;
  8  /


触发器已创建


SQL>
SQL> Create Or Replace Trigger trig_logoff
  2  Before
  3  logoff
  4  On Database
  5  Begin
  6    Insert Into log_table Values(ora_login_user,Null,Sysdate,ora_client_ip_ad
dress);
  7  End;
  8  /


触发器已创建


SQL> conn scott/tiger@local_orcl
已连接。
SQL> disconnect
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
SQL> conn scott/tiger@local_orcl
已连接。
SQL> disconnect
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
SQL> conn system/his@local_orcl
已连接。
SQL> select log_user,to_char(logon,'yyyymmdd hh24:mi:ss') as 登录时间,to_char(logoff,'yyyymmdd hh24:mi:ss') as 退出时间,ip_address as ip地址 from log_table;
LOG_USER             登录时间          退出时间          IP地址
-------------------- ----------------- ----------------- --------------------
SYS                  20150722 00:29:53                   
SYS                                    20150722 00:29:53 
SYSTEM                                 20150722 00:30:02 
SCOTT                20150722 00:30:02                   127.0.0.1
SCOTT                                  20150722 00:30:07 
SCOTT                20150722 00:30:10                   127.0.0.1
SCOTT                                  20150722 00:30:16 
SYSTEM               20150722 00:30:26                   127.0.0.1
SYSTEM               20150722 00:30:35                   127.0.0.1
SYS                  20150722 00:30:53                   
SYS                                    20150722 00:30:53 
SYS                  20150722 00:31:53                   
SYS                                    20150722 00:31:53 
13 rows selected




(四) 模式级触发器
     仅对该模式有效
Create Or Replace Trigger trigger_name
Befor | After
Create | Alter | Drop 
On Schema
PLSQL Block;


SQL> Create Table schema_table(event_name Varchar2(20),user_name varchar2(20),object_owner Varchar2(20),object_name Varchar2(250),object_type Varchar2(50),dt Date);
Table created


SQL> 
SQL> Create Or Replace Trigger trig_schema
  2  After
  3  Create Or Alter Or Drop
  4  On Schema
  5  Begin
  6    Insert Into schema_table Values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,Sysdate);
  7  End;
  8  /
Trigger created


SQL> create table tmp(id number);
Table created


SQL> alter table tmp add sname varchar2(20);
Table altered


SQL> alter table tmp drop column sname;
Table altered


SQL> select * from schema_table;
EVENT_NAME           USER_NAME            OBJECT_OWNER         OBJECT_NAME                                                                      OBJECT_TYPE                                        DT
-------------------- -------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------- -----------
CREATE               SCOTT                SCOTT                TMP                                                                              TABLE                                              2015/7/22 0
ALTER                SCOTT                SCOTT                TMP                                                                              TABLE                                              2015/7/22 0
ALTER                SCOTT                SCOTT                TMP                                                                              TABLE                                              2015/7/22 0


SQL>