我以前做的一个例子,你参考下吧 第八章 触发器(Trigger) --格式: 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 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 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;