触发器

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
create or replace trigger insert_emp_trigger
after insert on emp
for each row
  begin
    dbms_output.put_line('emp_insert_trigger'||'called:');
    dbms_output.put_line('添加员工：'||:old.empno||''||:old.ename);
  end;
  insert into emp(empno,ename) values(1,'ennames')
 create or replace trigger update_emp_trigger
 after update on emp
 for each row 
   begin
     dbms_output.put_line('Before update:'||:old.empno||'empname:'||:old.ename);
     dbms_output.put_line('After update:'||:new.empno||'empname:'||:new.ename);
   end;
create table accounttable(
customerName varchar2(30) primary key,
cardID VARCHAR2(8),
CurrentMoney number
);
insert into accounttable values('Daivd','10010001',5000);
insert into accounttable 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 GetMoney_Trigger
before insert on trans
for each row
  declare v_currentMoney accounttable.currentMoney%type;
  begin
    if :new.transtype='取款' then
       select currentmoney into v_currentMoney from accounttable where cardid = :new.cardid;
       if v_currentMoney < :new.transMoney then
         raise_application_error(-20001,'余额不足');
       end if;
       update accounttable set currentmoney = currentmoney - :new.transmoney where cardid = :new.cardid;
    else
      update accounttable 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;