Oracle学习:trigger

ZDNet软件频道 时间:2009-08-31 作者:Arraygrassbell | IT168 我要评论()
本文关键词:Oracle
3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log 表。2、 修改触发器,以便记录语句类型。是指为受到影响的各个行激活的触发器,定义与语句触发器类似,有以下两个例外:

  触发器

  是特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。

  功能:

  1、 允许/限制对表的修改

  2、 自动生成派生列,比如自增字段

  3、 强制数据一致性

  4、 提供审计和日志记录

  5、 防止无效的事务处理

  6、 启用复杂的业务逻辑

  开始

  create trigger biufer_employees_department_id

  before insert or update

  of department_id

  on employees

  referencing old as old_value

  new as new_value

  for each row

  when (new_value.department_id<>80 )

  begin

  :new_value.commission_pct :=0;

  end;

  /

  触发器的组成部分:

  1、 触发器名称

  2、 触发语句

  3、 触发器限制

  4、 触发操作

  1、 触发器名称

  create trigger biufer_employees_department_id

  命名习惯:

  biufer(before insert update for each row)

  employees 表名

  department_id 列名

  2、 触发语句

  比如:

  表或视图上的DML语句

  DDL语句

  数据库关闭或启动,startup shutdown 等等

  before insert or update

  of department_id

  on employees

  referencing old as old_value

  new as new_value

  for each row

  说明:

  1、 无论是否规定了department_id ,对employees表进行insert的时候

  2、 对employees表的department_id列进行update的时候

  3、 触发器限制

  when (new_value.department_id<>80 )

  限制不是必须的。此例表示如果列department_id不等于80的时候,触发器就会执行。

  其中的new_value是代表跟新之后的值。

  4、 触发操作

  是触发器的主体

  begin

  :new_value.commission_pct :=0;

  end;

  主体很简单,就是将更新后的commission_pct列置为0

  触发:

  insert into employees(employee_id,

  last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

  values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);

  select commission_pct from employees where employee_id=12345;

  触发器不会通知用户,便改变了用户的输入值。

  文本Tag: Oracle数据库开发

  触发器类型

  1、 语句触发器

  2、 行触发器

  3、 INSTEAD OF 触发器

  4、 系统条件触发器

  5、 用户事件触发器

  1、 语句触发器

  是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。比如,无论update多少行,也只会调用一次update语句触发器。

  例子:

  需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。

  Create table foo(a number);

  Create trigger biud_foo

  Before insert or update or delete

  On foo

  Begin

  If user not in (‘DONNY’) then

  Raise_application_error(-20001, ‘You don’t have access to modify this table.’);

  End if;

  End;

  /

  即使SYS,SYSTEM用户也不能修改foo表

  [试验]

  对修改表的时间、人物进行日志记录。

  1、 建立试验表

  create table employees_copy as select *from hr.employees

  2、 建立日志表

  create table employees_log(

  who varchar2(30),

  when date);

  3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log 表。

  Create or replace trigger biud_employee_copy

  Before insert or update or delete

  On employees_copy

  Begin

  Insert into employees_log(

  Who,when)

  Values( user, sysdate);

  End;

  /

  4、 测试

  update employees_copy set salary= salary*1.1;

  select *from employess_log;

  5、 确定是哪个语句起作用?

  即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?

  可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词,作判断:

  begin

  if inserting then

  -----

  elsif updating then

  -----

  elsif deleting then

  ------

  end if;

  end;

  if updating(‘COL1’) or updating(‘COL2’) then

  ------

  end if;

  [试验]

  1、 修改日志表

  alter table employees_log

  add (action varchar2(20));

  2、 修改触发器,以便记录语句类型。

  Create or replace trigger biud_employee_copy

  Before insert or update or delete

  On employees_copy

  Declare

  L_action employees_log.action%type;

  Begin

  if inserting then

  l_action:=’Insert’;

  elsif updating then

  l_action:=’Update’;

  elsif deleting then

  l_action:=’Delete’;

  else

  raise_application_error(-20001,’You should never ever get this error.’);

  Insert into employees_log(

  Who,action,when)

  Values( user, l_action,sysdate);

  End;

  /

  3、 测试

  insert into employees_copy( employee_id, last_name, email, hire_date, job_id)

  values(12345,’Chen’,’Donny@hotmail’,sysdate,12);

  select *from employees_log

  update employees_copy set salary=50000 where employee_id = 12345;

  2、 行触发器

  是指为受到影响的各个行激活的触发器,定义与语句触发器类似,有以下两个例外:

  1、 定义语句中包含FOR EACH ROW子句

  2、 在BEFORE……FOR EACH ROW触发器中,用户可以引用受到影响的行值。

  比如:

  定义:

  create trigger biufer_employees_department_id

  before insert or update

  of department_id

  on employees_copy

  referencing old as old_value

  new as new_value

  for each row

  when (new_value.department_id<>80 )

  begin

  :new_value.commission_pct :=0;

  end;

  /

  Referencing 子句:

  执行DML语句之前的值的默认名称是 :old ,之后的值是 :new

  insert 操作只有:new

  delete 操作只有 :old

  update 操作两者都有

  referencing子句只是将new 和old重命名为new_value和old_value,目的是避免混淆。比如操作一个名为new的表时。

  作用不很大。

  [试验]:为主健生成自增序列号

  drop table foo;

  create table foo(id number, data varchar2(20));

  create sequence foo_seq;

  create or replace trigger bifer_foo_id_pk

  before insert on foo

  for each row

  begin

  select foo_seq.nextval into :new.id from dual;

  end;

  /

  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, -- 与触发器相关的函数

  ora_dict_obj_type,

  sysdate);

  end;

  /

  3. 测试

  create table drop_me(a number);

  create view drop_me_view as select *from drop_me;

  drop view drop_me_view;

  drop table drop_me;

  select *from droped_objects

  禁用和启用触发器

  alter trigger disable;

  alter trigger enable;

  事务处理:

  在触发器中,不能使用commit / rollback

  因为ddl语句具有隐式的commit,所以也不允许使用

  视图:

  dba_triggers


百度大联盟认证黄金会员Copyright© 1997- CNET Networks 版权所有。 ZDNet 是CNET Networks公司注册服务商标。
中华人民共和国电信与信息服务业务经营许可证编号:京ICP证010391号 京ICP备09041801号-159
京公网安备:1101082134