分享:oracle触发器的经典实例

--格式：
create or replace trigger 名称
[after|before][delete|update|insert]
[on 表 | on schema]
[referencing new as 别名 old as 别名]
[for each row]
declare
  ....
begin
 ...
exception
 ....
end;

--创建一个delete类型行级触发器
--删除的一行数据保存在:old
create or replace trigger emp_delete_row_trigger
after delete
on emp
referencing new as n old as o
for each row
begin
  dbms_output.put_line('emp_delete_row_trigger called.');
  dbms_output.put_line('删除员工：' || :o.empno || ' ' || :o.ename);
end;

delete from emp where empno=7499;
delete from emp;
--创建一个insert类型行级触发器
--插入的一行新数据保存在:new
create or replace trigger emp_insert_row_trigger
after insert
on emp
for each row
begin -- www.jb200.com
  dbms_output.put_line('emp_insert_row_trigger called.');
  dbms_output.put_line('添加员工：' || :new.empno || ' ' || :new.ename);
end;

insert into emp(empno,ename) values(1,'empxxx');

--创建一个update类型行级触发器
--修改前的数据保存在:old
--修改后的数据保存在:new
create or replace trigger emp_update_row_trigger
after update
on emp
for each row
begin
  dbms_output.put_line('emp_update_row_trigger called.');
  dbms_output.put_line('修改前：' || :old.empno || ' ' || :old.ename);
  dbms_output.put_line('修改后：' || :new.empno || ' ' || :new.ename);
end;

update emp
set ename='xxxx'
where empno=7499;

--语句级触发器(update,delete,insert)
create or replace trigger delete_stmt_trigger
after delete
on emp
begin
  dbms_output.put_line('delete_stmt_trigger called.');
end;

delete from emp;
--判断触发器类型

----------------------------------------------------------
--每进行一次交易，就要调用触发器，自动扣除或增加账户金额
----------------------------------------------------------
create table account
(
  customerName varchar2(30) primary key,
  cardID varchar2(8),
  currentMoney number
);
insert into account values('Daivd','10010001',5000);
insert into account values('Jason','10010002',3000);

create table trans
(
  transDate date,
  cardID varchar2(8),
  transType varchar2(10),
  transMoney number
);
insert into trans
values(sysdate,'10010001','取款',1000);

create or replace trigger trans_trigger
before insert
on trans
for each row
declare -- www.jb200.com
  v_currentMoney account.currentMoney%type;
begin
  --判断类型
  if :new.transType='取款' then
   --取款
   select currentMoney into v_currentMoney
   from account
   where cardID=:new.cardID;
  
   if v_currentMoney < :new.transMoney then
     raise_application_error(-20001,'余额不足');
   end if;
  
   update account
   set currentMoney=currentMoney-:new.transMoney
   where cardID=:new.cardID;
  else
   --存款
   update account
   set currentMoney=currentMoney+:new.transMoney
   where cardID=:new.cardID;
  end if;
exception 
  when no_data_found then
    raise_application_error(-20002,'无效的帐户');
end;

--模式(schema)级触发器
create or replace trigger schema_trigger
before drop
on schema
begin
  dbms_output.put_line('schema_trigger called');
  dbms_output.put_line(ora_dict_obj_name);
  dbms_output.put_line(ora_dict_obj_type);
 
  if ora_dict_obj_name='ACCOUNT' then
    raise_application_error(-20003,'ACCOUNT表不能被删除');
  end if;
end;

drop table account;
--ora_dict_obj_name 操作对象名称
--ora_dict_obj_type 操作对象类型

--启用触发器
alter trigger schema_trigger enable;
--禁用触发器
alter trigger schema_trigger disable;