oracle触发器

定义：


如何实现跟踪并记录所有对教师表的表结构进行改变的操作


只要一变动表结构就要记录所有操作到一个日志表中


不需要显式调用来执行，而是修改表结构的操作来启动运行


​


​示例语法：


CREATE [OR REPLACE] TRIGGER  trigger_name


AFTER | BEFORE | INSTEAD OF


[INSERT] [[OR] UPDATE [OF column_list]] 


[[OR] DELETE]


ON  table_or_view_name


[REFERENCING {OLD [AS] old / NEW [AS] new}]


[FOR EACH ROW]


[WHEN (condition)]


pl/sql_block;


​


示例1：


create or replace trigger tri_insert_teacher


  after insert on teacher  


  for each row


declare


  begin


  dbms_output.put_line(:old.sal||'-'||:new.sal);


end tri_insert_teacher;


insert into teacher values('22', '孙悟空', 10000, '1', '1');


create or replace trigger tri_update_teacher


  after update on teacher  


  for each row


declare


  begin


  dbms_output.put_line(:old.sal||'-'||:new.sal);


end tri_update_teacher;


update teacher set sal = 20000 where tno = '22';


​create or replace trigger tri_delete_teacher


  after delete on teacher  


  for each row


declare


  begin


  dbms_output.put_line(:old.sal||'-'||:new.sal);


  if :old.sal = :new.sal and :new.sal > 1600 then


    RAISE_APPLICATION_ERROR(-20001, 'wakaka', false);


  end if;


end tri_update_teacher;


delete from teacher where tno = '1';


select * from teacher where tno = '1';


示例2：


​--梦想基金


create table tb_money


(


 salary number


);


insert into tb_money values(100000000);


select * from tb_money;


--梦想


create table tb_dream


(


 dream nvarchar2(100),


 money number


);


create or replace trigger tri_delete_teacher


  after delete on teacher  


  for each row


declare


  


begin


end tri_delete_teacher;


create or replace trigger tri_dreaming


  before insert on tb_dream


  for each row


  declare


    v_money number;


  begin


    --获取梦想基金剩余金额


    select nvl(sum(salary), 0) into v_money from tb_money;


    


    if v_money <= 0 then


      dbms_output.put_line('没有资金了，正在联系赞助...');


    elsif v_money < :new.money then


      dbms_output.put_line('资金不足，分步走...');


    else


      dbms_output.put_line('去实现你的梦想吧...');


      update tb_money set salary = salary - :new.money;


    end if;    


end tri_dreaming;


insert into tb_dream values('环游世界，飞跃地球', 600);


insert into tb_dream values('收购公司', 600);


insert into tb_dream values('帮大家实现梦想', -2000000);


insert into tb_dream values('开发时光机，回到过去，研究项目', 60000);


insert into tb_dream values('买架私人飞机', 20000);






select * from tb_dream;


select * from tb_money;