oracle触发器详细(3)

insert into foo(data) values(‘donny’);
insert into foo values(5,’Chen’);
select * from foo;

3、 INSTEAD OF 触发器更新视图

Create or replace view company_phone_book as
 Select first_name||’, ’||last_name name, email, phone_number,
employee_id emp_id
From hr.employees;

尝试更新email和name
update hr.company_phone_book
 set name=’Chen1, Donny1’
where emp_id=100

create or replace trigger update_name_company_phone_book
INSTEAD OF
Update on hr.company_phone_book
Begin
 Update hr.employees
  Set employee_id=:new.emp_id,
   First_name=substr(:new.name, instr(:new.name,’,’)+2),
   last_name= substr(:new.name,1,instr(:new.name,’,’)-1),
   phone_number=:new.phone_number,
   email=:new.email
 where employee_id=:old.emp_id;
end;

4、 系统事件触发器
系统事件：数据库启动、关闭，服务器错误

create trigger ad_startup
 after startup
  on database
begin
 -- do some stuff
end;
/

5、 用户事件触发器
用户事件：用户登陆、注销，CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE /

RENAME / TRUNCATE / LOGOFF

例子：记录删除对象

1. 日志表
create table droped_objects(
 object_name varchar2(30),
 object_type varchar2(30),
 dropped_on date);

2．触发器
create or replace trigger log_drop_trigger
 before drop on donny.schema
begin
 insert into droped_objects values(
  ora_dict_obj_name,  -- 与触发器相关的函数
  o