触发器的原理和使用方法

触发器

---

触发器的定义：

q触发器是当特定事件出现时自动执行的存储过程

q特定事件可以是执行更新的DML语句和DDL语句

q触发器不能被显式调用

q触发器的功能：

q自动生成数据

q自定义复杂的安全权限

q提供审计和日志记录

q启用复杂的业务逻辑

触发器的语法：

CREATE [OR REPLACE] TRIGGER trigger_name

AFTER | BEFORE | INSTEAD OF

[INSERT] [[OR] UPDATE [OF column_list]]

[[OR] DELETE]

ON table_or_view_name

[REFERENCING {OLD [AS] old / NEW [AS] new}]

[FOR EACH ROW]

[WHEN (condition)]

pl/sql_block;

触发器由三部分组成：

q触发器语句（事件）

q定义激活触发器的 DML 事件和 DDL 事件

q触发器限制

q执行触发器的条件，该条件必须为真才能激活触发器

q触发器操作（主体）

q包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

触发器按执行次序分为：before触发器、after触发器

before触发器：

---

after触发器：

---

for each row：

---

createtablestudent(idnumber(10),namevarchar2(20),agenumber(10));

insertintostudentvalues(1,'张三',20);

insertintostudentvalues(2,'李四',25);

insertintostudentvalues(3,'王五',30);

createorreplacetriggertrigger1

afterupdate--after触发器，并且针对于更新操作

onstudent--触发器针对哪张表

foreachrow-- 添加了

begin

dbms_output.put_line('更新了');

end;

-- ORA-04089: 无法对 SYS 拥有的对象创建触发器

SQL>connecthr/hr@jiagulun

updatestudent ssets.age=s.age+5;

SQL>setserverouton;

SQL>updatestudent ssets.age=s.age+5;

更新了--触发器触发了

更新了--触发器触发了

更新了--触发器触发了

3rowsupdated

--更新了三行触发了三次

SQL>

createorreplacetriggertrigger1

afterupdate

onstudent

foreachrow-- 没有添加了

begin

dbms_output.put_line('更新了');

end;

SQL>updatestudent ssets.age=s.age+5;

更新了--触发器触发了

3rowsupdated

--更新了三行触发了一次

SQL>

通过上面的测试可以得出：

1.对于sys用户所拥有的对象是不能建立触发器的。

2.for each row：

1.假如添加了说明是行级别的触发器，每行的更新都会触发。

2. 假如没添加就是表级别的触发器，所有的更新行只会触发一次。

：new 和 ：old的区别：

---

：new 和sqlserver中的inserted
：old 和sqlserver中的deleteed

如果在触发器的plsql内使用了：new 和 ：old，就必须使用行级触发器也就是for each row
因为：new和：old是指向某行记录的指针，假如是表级别的触发器这两个指针不知道确定指向哪行记录
这样就会产生歧义了

1. 当执行insert 的时候： ：new存在 ：old不存在。
2. 当执行delete 的时候： ：new不存在 ：old存在。
3. 当执行update 的时候： ：new存在 ：old存在。

当一个update被执行的时候：

oracle是先删除记录行，所以old是需要的

oracle再insert记录行，所以new是需要的

createorreplacetriggertrigger2

beforeupdate

onstudent

foreachrow

begin

if:old.age>20or:new.age<30then

raise_application_error(-20001,'大于20岁的学生不需要更新，而且更新后的年龄不能大于30');

endif;

end;

SQL>updatestudent ssets.age=s.age+10;

updatestudent ssets.age=s.age+10

--ORA-20001: 大于20岁的学生不需要更新，而且更新后的年龄不能大于30

--ORA-06512: 在 "HR.TRIGGER2", line 3

--ORA-04088: 触发器 'HR.TRIGGER2' 执行过程中出错

createorreplacetriggertrigger2

beforeupdate

onstudent

--foreachrow使用了new或old就必须使用行级触发器

begin

if:old.age>20or:new.age<30then

raise_application_error(-20001,'大于20岁的学生不需要更新，而且更新后的年龄不能大于30');

endif;

end;

--ORA-04082: NEW 或 OLD 引用不允许在表级触发器中

SQL>

通过上面的操作可以发现：
1.NEW 或 OLD 引用不允许在表级触发器中
2. 在update中既有new 也有 old
3. 在触发器中不能使用alter、create、事务回滚、

createorreplacetriggertrigger3

afterinsert--对于before 类型 insert操作的触发器，：new是不能被修改的；只能在before情况下

onstudent

foreachrow

begin

if:new.age<0then

:new.age:=-:new.age;

endif;

end;

--ORA-04084: 无法更改此触发器类型的 NEW 值

createorreplacetriggertrigger3

before/afterdelete

onstudent

foreachrow

begin

if:old.age>0then

:old.age := - :old.age ;--无论在before还是after中都不能更改

endif;

end;

--ORA-04085: 无法更改 OLD 引用变量的值

触发器的类型：

---

instead of触发器：

---

SQL>createviewstu_add_view

2asselects.id,s.name,s.age,a.zzfromstudent sinnerjoinaddress a

3ons.id=a.xh;

Viewcreated

SQL>select*fromstu_add_view;

IDNAME AGE ZZ

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

2李四40郑州

1张三35开封

3王五45洛阳

SQL>updatestu_add_view savsetsav.zz='江西'wheresav.name='王五';

updatestu_add_view savsetsav.zz='江西'wheresav.name='王五';

--ORA-01779: 无法修改与非键值保存表对应的列

--对于上面的错误，可以通过触发器来解决：

createorreplacetriggertrigger4insteadofupdateonstu_add_viewforeachrow

declare

xh3number(10);--变量的声明不要和列名相同，因为会出现下面问题：

begin

selects.idintoxh3fromstudent swheres.name=:old.name;

delete address awherea.xh=xh3;--这里，假如上面的声明是xh那么就会出现a.xh = xh这会出现恒等的情况，会删除所有记录

insertintoaddressvalues(xh3,:new.zz);

end;

其实instead of 触发器的sql.block中做的操作就是底层更新的操作：先删除后插入

-------------------------写一个触发器：所有的dml操作都输出操作信息----------

createorreplacetriggertrigger6beforeinsertordeleteorupdateonstudentforeachrow

begin

ifinsertingthen

dbms_output.put_line('进行的是插入操作插入的信息如下：姓名:'||:new.name||'年龄:'||:new.age);

elsifdeletingthen

dbms_output.put_line('进行的是删除操作删除的信息如下：姓名:'||:old.name||'年龄:'||:old.age);

elsifupdatingthen

dbms_output.put_line('进行的是更新操作原信息如下：姓名:'||:new.name||'年龄:'||:new.age);

dbms_output.put_line('新信息如下姓名:'||:old.name||'年龄:'||:old.age);

else

dbms_output.put_line('做的是其他操作');

endif;

end;

--------------写一个模式触发器----------------------------

createtabledropped_obj(obj_namevarchar2(20),obj_typevarchar2(20),drop_datedate);

createorreplacetriggertrigger1beforealterordroponschema--针对于该用户对象的删除操作进行触发

begin

insertintodropped_objvalues(ora_dict_obj_name,ora_dict_obj_type,sysdate);

end;

SQL>droptablestudent;

Tabledropped

SQL>select*fromdropped_obj;--会发现刚刚删除的操作被记录下来

OBJ_NAME OBJ_TYPE DROP_DATE

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

STUDENTTABLE19-1月-156:

SQL>

--使用的变量：

--Ora_client_ip_address 返回客户端的ip地址

--Ora_database_name 返回当前数据库名

--Ora_login_user 返回登录用户名

--Ora_dict_obj_name 返回ddl操作所对应的数据库对象名

--Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型

--------------写一个数据库触发器----------------------------

createtableevent_table(eventvarchar2(30),timedate);

createorreplacetriggertrigger_startafter startupondatabase

begin

insertintoevent_tablevalues(ora_sysevent,sysdate);

end;

createorreplacetriggertrigger_shutbeforeshutdownondatabase;

begin

insertintoevent_tablevalues(ora_sysevent,sysdate);

end;

SQL>shutdownimmediate;--关闭数据库事件

Databaseclosed.

Databasedismounted.

ORACLEinstanceshut down.

SQL>startup;

ORACLEinstancestarted.--开启数据库事件

TotalSystemGlobalArea400846848bytes

FixedSize2213776bytes

VariableSize251660400bytes

DatabaseBuffers142606336bytes

Redo Buffers4366336bytes

Databasemounted.

Databaseopened.

SQL>select*fromevent_table;--两个事件都被记录下来了

EVENTTIME

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

SHUTDOWN19-JAN-15

STARTUP19-JAN-15

SQL>

--------------写一个登录触发器触发器----------------------------

createtablelog_table(usernamevarchar2(20),logon_timedate,logoff_timedate,addressvarchar2(20));

createorreplacetriggertrigger_loginafterlogonondatabase

begin

insertintolog_tablevalues(Ora_login_user,sysdate,null,Ora_client_ip_address);

end;

createorreplacetriggertrigger_logoutbeforelogoffondatabase

begin

updatelog_table ltsetlt.logoff_time=sysdatewherelt.username=Ora_login_user;

end;

SQL>exit;--退出操作

DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64bit Production

WiththePartitioning,OLAP,DataMiningandRealApplication Testing options

[oracle@localhost ~]$sqlplus/assysdba--登录操作

SQL*Plus:Release11.2.0.1.0 ProductiononMon Jan1906:37:492015

Copyright(c)1982,2009,Oracle.Allrights reserved.

Connectedto:

OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64bit Production

WiththePartitioning,OLAP,DataMiningandRealApplication Testing options

SQL>select*fromlog_table;

USERNAME LOGON_TIME LOGOFF_TIME ADDRESS

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

SYS19-JAN-1519-JAN-15

SYS19-JAN-15

6rowsselected.

SQL>