触发器

--触发器:被隐含执行的存储过程,发生特定事件，自动执行触发器代码
-- 类别：DML触发器(insert,update,delete)，替代触发器(只能应用于视图),系统触发器(系统登陆，退出)
         触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句， 不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）
--触发器代码的大小不能超过32K


  创建触发器的语法结构
      create [or replace] trigger trigger_name(自定义触发器名称) 
      timing event1[or event2 or event3] 
      on 触发对象(表,视图，数据库) 更新表的某个字段 update of 字段名 on 表名
      begin 
      .....
      end;


 raise_application_error(-20000,'不能在周四和周五修改员工信息'); //印发系统级别的应用错误，提示给客户端
 代码取值范围 ：-20000 -- 20999 避免根系统的错误代码相冲突


 创建BEFORE语句触发器


 -- 限定员工表不能修改
 create or replace trigger trigger_emp 
 before insert or update or delete  
 on emp 
 begin 
 if to_char(sysdate,'day') in('星期四','星期五') then 
 raise_application_error(-20000,'不能在周四和周五修改员工信息');
 end if;
 end;


 --修改员工信息以后，插入日志信息表(记录修改前和修改后的值)


 触发器代码中区分具体的触发事件，可以使用以下3个条件谓词 
 inserting,updating,deleting --目的区分具体的触发事件


 create or replace trigger trigger_emp 
 before insert or update or delete  
 on emp 
 begin 
 if to_char(sysdate,'day') in('星期四','星期五') then 
 case 
 when inserting then
 raise_application_error(-20000,'不能在周四和周五添加员工信息');
 when updating then
 raise_application_error(-20001,'不能在周四和周五修改员工信息');
 when deleting then
 raise_application_error(-20002,'不能在周四和周五删除员工信息');
 end case;
 end if;
 end;


 -- 行记录 for each row


 create or replace trigger trigger_emp_deptno 
 before update or delete on emp for each row when(old.deptno=30)  //定义事件上不加: 
 begin 
 case 
 when deleting then 
 raise_application_error(-20004,'不能删除该部门的员工信息');
 when updating then 
 if :new.sal<:old.sal then 
 raise_application_error(-20004,'修改后的薪资比原薪资小，不能修改');
 end if;
 if :new.comm<:old.comm then 
 raise_application_error(-20004,'修改后的奖金比原奖金小，不能修改');
 end if;
 end case;
end;




--修改了主表dept中的deptno之后（AFTER），级联的、自动的更新子表emp中原来在该部门的deptno。 


create or replace trigger trigger_dept_udpate 
after update of deptno on dept for each row 
begin 
update emp set deptno=:new.deptno where deptno=:old.deptno;
dbms_output.put_line('员工表更新成功!');
end;




--instead of 替代触发器 只能应用于视图


--不能指定BEFORE和AFTER选项
--必须指定FOR EACH ROW选项
-- 创建视图
create or replace view view_emp_count as select deptno as deptno,count(empno) as emp_count,sum(sal) as sum_sal,avg(sal) as avg_sal from emp2 group by deptno;




create or replace trigger trigger_view 
    instead of delete on view_emp_count for each row 
    begin 
    delete from emp2 where deptno=:old.deptno;
    end;


  -- 创建系统事件触发器 
 监控数据库的关闭
 create or replace trigger trigger_db 
 before shutdown on database 
 begin
insert into event_table values(ora_sysevent,sysdate);
 end;


 --记载用户的登录和退出事件


  create or table log_event(
  username varchar2(20),
  ipAddress varchar2(20),
  logonTime timestamp,
  logoffTime timestamp
)
--用户退出触发器
create or replace trigger trigger_log 
before logoff on database 
begin 
insert into log_event(username,ipAddress,logoffTime) values(ora_login_user,ora_client_ip_address,sysdate);
end;


--用户登陆触发器
create or replace trigger trigger_log 
after logon on database 
begin 
insert into log_event(username,ipAddress,logonTime) values(ora_login_user,ora_client_ip_address,sysdate);
end;