【Oracle】触发器——题

--建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。 

--建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
create table emp_his as select * from emp; 
create or replace trigger tr_del_emp 
   before delete --指定触发时机为删除操作前触发
   on emp 
   for each row   --说明创建的是行级触发器 
begin
   --将修改前数据插入到日志记录表 emp_his ,以供监督使用。
   insert into emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       values( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
       dbms_output.put_line('插入成功');
end;

delete emp where empno=7788;

drop table emp_his;
drop trigger tr_del_emp;
INSERT INTO EMP VALUES
(7788,'SCOTT','CLERK',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20);

--限制对Dept表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改dept表。 

--限制对Dept表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改dept表。
create or replace trigger my_tri before insert or delete or update on dept
begin
   if (to_char(sysdate,'day') in ('星期六', '星期日')) or (to_char(sysdate, 'HH24:MI') not between '08:30' and '18:00') then
     raise_application_error(-20001, '不是上班时间，不能修改dept表');
 end if;
end;
delete dept where deptno=60;
insert into dept values(60,'aa','威海');

 --限定只对部门号为20的记录进行行触发器操作。

--限定只对部门号为20的记录进行行触发器操作。
create or replace trigger my_tri_no before delete or update of sal,comm on emp
for each row
when(old.deptno=20)
begin
case
when updating('sal') then
   if :new.sal<:old.sal then
       raise_application_error(-20001, '部门80的人员的工资不能降');
           end if;
when updating('comm') then
   if :new.comm<:old.comm then
       raise_application_error(-20002, '部门80的人员的奖金不能降');
           end if;
when deleting then
          raise_application_error(-20003, '不能删除部门80的人员记录');
     end case;
end;
update emp set sal = 2000 where deptno = 20;
delete from emp where deptno=20;

 创建触发器删除10部门的信息

create or replace view emp_view as --创建视图
select deptno, count(*) total_employeer, sum(sal) total_salary 
from emp group by deptno;

create or replace trigger emp_view_delete --创建触发器删除
   instead of delete on emp_view for each row
begin
   delete from emp where deptno= :old.deptno;
end;

select * from emp_view;
delete from emp_view where deptno=10; 
drop trigger emp_view_delete;
drop view emp_view; 

 创建系统事件触发器——

DDL触发器

例：记录用户所有的DDL操作 

create table ddl_event(
 crt_date timestamp PRIMARY KEY,
 event_name VARCHAR2(20), 
 user_name VARCHAR2(10),
 obj_type VARCHAR2(20),
 obj_name VARCHAR2(20));
 
 create or replace table trigger tr_ddl
after ddl on schema
begin
   insert into ddl_event values(systimestamp, ora_sysevent, ora_login_user, ora_dict_obj_type, ora_dict_obj_name);
end;

create table a(n number);
drop table a;
select * from ddl_event;

登录触发器——退出触发器 

create table event(
  eventuser varchar(60),
  action varchar(30)
);
drop table event;
create or replace trigger login_trig
after logon on database
begin
  insert into event(eventuser, action) values(user,'log on');
end;
create or replace trigger login_trig
before logoff on database
begin
  insert into event(eventuser, action) values(user,'log off');
end;

禁用或启用触发器 

ALTER TIGGER trigger_name [DISABLE | ENABLE ];