触发器

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。create or replace trigger insert_emp_triggerafter insert on empfor 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_Triggerbefore insert on transfor 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_triggerbefore dropon schemabegin  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;