oracle 触发器1

存放在数据库中，并被隐含执行的存储过程。在Oracle8i之前，只允许给予表或者视图的的DML的操作，而从Oracle8i开始，不仅可以支持DML触发器，也允许给予系统事件和DDL的操作。

 

--Before语句触发器
--禁止工作人员在休息日改变雇员信息
create or replace trigger tr_src_emp
  before insert or update or delete on emp
  begin
    if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then
      raise_application_error(-20001,'can not modify user information in weekend');
    end if;
  end;
/

--使用条件谓语
create or replace trigger tr_src_emp
  before insert or update or delete on emp
  begin
    if to_char(sysdate,'DY') in ('星期六','星期天') then
      case
        when inserting then
          raise_application_error(-20001,'fail to insert');
        when updating then
          raise_application_error(-20001,'fail to updating');
        when deleting then
          raise_application_error(-20001,'fail to delete');
      end case;
    end if;
  end;
/

 

--after语句触发器
--例如：为了统计在EMP表上的增、删、改的次数。先建一张表
create table audit_table(
    Name varchar2(20),
    ins int,
    upd int,
    del int,
    starttime date,
    endtime date
  );

 

create or replace trigger tr_audit_emp
after insert or update or delete on emp
declare
  v_temp int;
  begin
    select count(*) into v_temp from audit_table
    where name='EMP';
    if v_temp=0 then
      insert into audit_table values('EMP',0,0,0,sysdate,null);
    end if;
    case
      when inserting then
        update audit_table set ins=ins+1,endtime=sysdate where name='EMP';
      when updating then
        update audit_table set upd=upd+1,endtime=sysdate where name='EMP';
      when deleting then
        update audit_table set del=del+1,endtime=sysdate where name='EMP';
    end case;
  end;
/

 

-- 行触发器 执行DML操作时，每作用一行就触发一次触发器。
-- Before行触发器
-- 例如：确保员工工资不能低于原有工资
create or replace trigger tr_emp_sal
before update of sal on emp
for each row
  begin
    if :new.sal<:old.sal then
      raise_application_error(-20010,'sal should not be less');
    end if;
  end;
/

 

-- after行触发器
-- 例如：统计员工工资变化
  Create table audit_emp_change(
    Name varchar2(10),
    Oldsal number(6,2),
    Newsal number(6,2),
    Time date
   );

 

create or replace trigger tr_sal_sal
after update of sal on emp
for each row
  declare
    v_temp int;
    begin
      select count(*) into v_temp from audit_emp_change where name=:old.ename;
      if v_temp=0 then
        insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
      else
        update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
      end if;
   end;
/

 

-- 限制行触发器
create or replace trigger tr_sal_sal
after update of sal on emp
for each row
   when (old.job='SALESMAN')
     declare
       v_temp int;
       begin
         select count(*) into v_temp from audit_emp_change where name=:old.ename;
         if v_temp=0 then
            insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
         else
            update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
        end if;
    end;
/

 

 

-- 注意事项

编写DML触发器的时，触发器代码不能从触发器所对应的基表中读取数据。
例如：如果要基于EMP表建立触发器。那么该触发器的执行代码不能包含对EMP表的查询操作。
Create or replace trigger tr_emp_sal
  Before update of sal on emp
  For each row
   declare
     Maxsal number(6,2);
     Begin
      select max(sal) into maxsal from emp;
      If :new.sal>maxsal then
        Raise_application_error(-21000,'error');
      End if;
   End;
/

创建的时候不会报错。但是一旦执行就报错了 update emp set sal=sal*1.1 where deptno=30

 

-- 触发器的主要用途

控制数据安全  例如：在非工作时间不能对EMP表做操作

create or replace trigger tr_emp_time
before insert or update or delete on emp
begin
  if to_char(sysdate,'HH24') not between '9' AND '17' THEN
    raise_application_error(-20101,'not work time');
  end if;
end;
/

 

实现数据统计 例如：上面提到的记载员工的工资变化等

 

实现数据的完整性

例如：如果只是限制员工的工资不能低于800，可以选用check约束
Alter table emp add constraint ck_sal check(sal>=800);
但如果是限定新工资不能低于其原来工资，也不能高于20%。则通过约束是无法实现的。这时可通过触发器来实现.

Create or replace trigger tr_check_sal
Before update of sal on emp
For each row
  When(new.sal<old.sal or new.sal>1.2*old.sal)
  Begin
    Raise_application_error(-20931,'ddd');
  End;
/

 

实现参照完整性 约束可实现级联删除，却不能实现级联更新.可通过触发器实现.

Create or replace trigger tr_update_cascade
after update of deptno on dept
for each row
begin
   update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
/