触发器，oracle触发器
oracle222
触发器
触发器的定义：
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：
|
create table student (id number (10 ),name varchar2 (20 ),age number( 10));
insert into student values( 1, '张三',20);
insert into student values( 2, '李四',25);
insert into student values( 3, '王五',30);
create or replace trigger trigger1 after update --after触发器，并且针对于更新操作 on student --触发器针对哪张表 for each row -- 添加了 begin dbms_output.put_line('更新了' ); end; -- ORA-04089: 无法对 SYS 拥有的对象创建触发器 SQL >connect hr/ hr@ jiagulun update student s set s.age = s.age +5 ; SQL> set serverout on; SQL> update student s set s.age = s.age +5 ; 更新了--触发器触发了 更新了--触发器触发了 更新了--触发器触发了 3 rows updated --更新了三行触发了三次 SQL> create or replace trigger trigger1 after update on student SQL> update student s set s.age = s.age +5 ; 更新了--触发器触发了 3 rows updated --更新了三行触发了一次 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是指向某行记录的指针，假如是表级别的触发器这两个指针不知道确定指向哪行记录 这样就会产生歧义了 当一个update被执行的时候： oracle是先删除记录行，所以old是需要的 oracle再insert记录行，所以new是需要的 create or replace trigger trigger2 before update on student for each row begin if :old.age > 20 or :new.age < 30 then raise_application_error (-20001 ,'大于20岁的学生不需要更新，而且更新后的年龄不能大于30' ); end if; end; SQL> update student s set s.age = s.age + 10; update student s set s.age = s.age + 10 --ORA-20001: 大于20岁的学生不需要更新，而且更新后的年龄不能大于30 --ORA-06512: 在 "HR.TRIGGER2", line 3 --ORA-04088: 触发器 'HR.TRIGGER2' 执行过程中出错 create or replace trigger trigger2 before update on student --for each row 使用了new或old就必须使用行级触发器 begin if :old.age > 20 or :new.age < 30 then raise_application_error (-20001 ,'大于20岁的学生不需要更新，而且更新后的年龄不能大于30' ); end if; end; --ORA-04082: NEW 或 OLD 引用不允许在表级触发器中 SQL> 通过上面的操作可以发现： 1. NEW 或 OLD 引用不允许在表级触发器中 2. 在update中既有new 也有 old 3. 在触发器中不能使用alter、create、事务回滚、 create or replace trigger trigger3 --ORA-04084: 无法更改此触发器类型的 NEW 值 create or replace trigger trigger3 before/after delete on student for each row begin if :old.age > 0 then |
触发器的类型：
.png)
.png)
instead of触发器：
|
SQL> create view stu_add_view
2 as select s.id, s.name, s.age, a.zz from student
s inner join address a
3 on s.id = a.xh;
View created
SQL> select * from stu_add_view; ID NAME AGE ZZ ----------- -------------------- ----------- ---------- 2 李四 40 郑州 1 张三 35 开封 3 王五 45 洛阳 SQL> update stu_add_view sav set sav.zz = '江西' where sav.name = '王五'; update stu_add_view sav set sav.zz = '江西' where sav.name = '王五'; --ORA-01779: 无法修改与非键值保存表对应的列 --对于上面的错误，可以通过触发器来解决： create or replace trigger trigger4 instead of update on stu_add_view for each row declare xh3 number (10 );--变量的声明不要和列名相同，因为会出现下面问题： begin select s.id into xh3 from student s where s.name = : old.name; delete address a where a.xh = xh3;--这里，假如上面的声明是xh那么就会出现a.xh = xh这会出现恒等的情况，会删除所有记录 insert into address values (xh3 ,:new.zz ); end ; 其实instead of 触发器的sql.block中做的操作就是底层更新的操作：先删除后插入 -------------------------写一个触发器：所有的dml操作都输出操作信息---------- create or replace trigger trigger6 before insert or delete or update on student for each row begin if inserting then dbms_output.put_line('进行的是插入操作插入的信息如下：姓名:' ||:new.name ||'年龄:' ||:new.age ); elsif deleting then dbms_output.put_line ('进行的是删除操作删除的信息如下：姓名:' ||:old.name ||'年龄:' ||:old.age ); elsif updating then dbms_output.put_line ('进行的是更新操作原信息如下：姓名:' ||:new.name ||'年龄:' ||:new.age ); dbms_output.put_line ('新信息如下姓名:' ||:old.name ||'年龄:' ||:old.age ); else dbms_output.put_line ('做的是其他操作' ); end if; end; |
|
--------------写一个模式触发器----------------------------
create table dropped_obj (obj_name varchar2( 20), obj_type varchar2(20),drop_date date);
create or replace trigger trigger1 before alter or drop on schema --针对于该用户对象的删除操作进行触发 begin insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate ); end ; SQL> drop table student; Table dropped SQL> select * from dropped_obj;--会发现刚刚删除的操作被记录下来 OBJ_NAME OBJ_TYPE DROP_DATE -------------------- -------------------- ----------- STUDENT TABLE 19- 1月 -15 6 : SQL> --使用的变量： --Ora_client_ip_address 返回客户端的ip地址 --Ora_database_name 返回当前数据库名 --Ora_login_user 返回登录用户名 --Ora_dict_obj_name 返回ddl操作所对应的数据库对象名 --Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型 --------------写一个数据库触发器---------------------------- create table event_table ( event varchar2( 30), time date ); create or replace trigger trigger_start after startup on database begin insert into event_table values (ora_sysevent ,sysdate ); end ; create or replace trigger trigger_shut before shutdown on database; begin insert into event_table values (ora_sysevent ,sysdate ); end ; SQL> shutdown immediate; --关闭数据库事件 Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.--开启数据库事件 Total System Global Area 400846848 bytes Fixed Size 2213776 bytes Variable Size 251660400 bytes Database Buffers 142606336 bytes Redo Buffers 4366336 bytes Database mounted. Database opened. SQL> select * from event_table;--两个事件都被记录下来了 EVENT TIME ------------------------------ ------------ SHUTDOWN 19- JAN- 15 STARTUP 19- JAN- 15 SQL> --------------写一个登录触发器触发器---------------------------- create table log_table (username varchar2( 20), logon_time date , logoff_time date, address varchar2( 20)); create or replace trigger trigger_login after logon on database begin insert into log_table values(Ora_login_user,sysdate ,null ,Ora_client_ip_address ); end ; create or replace trigger trigger_logout before logoff on database begin update log_table lt set lt.logoff_time = sysdate where lt.username = Ora_login_user; end; SQL> exit;--退出操作 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning , OLAP , Data Mining and Real Application Testing options [oracle@ localhost ~]$ sqlplus / as sysdba --登录操作 SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 06: 37: 49 2015 Copyright ( c) 1982 , 2009, Oracle. All rights reserved. Connected to : Oracle Database 11 g Enterprise Edition Release 11.2 .0.1.0 - 64 bit Production With the Partitioning , OLAP , Data Mining and Real Application Testing options SQL> select * from log_table; USERNAME LOGON_TIME LOGOFF_TIME ADDRESS -------------------- ------------ ------------ -------------------- SYS 19- JAN- 15 19- JAN- 15 SYS 19- JAN- 15 6 rows selected. SQL> |
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。