触发器

-- 增加一张表xs_1，用来存放从xs表中删除的记录
create table xs_1 as select * from xs;

truncate table xs_1;

create or replace trigger t1
    before delete on xs
    for each now
      
begin
     insert into xs_1(xh,xm,zym,xb,cssj,zxf) values(:old.xh,:old.xm,:old.zym,:old.xb,:old.cssj,:old.zxf); 
end t1;    


-- 监控用户对xs表的操作，要求：当xs表执行插入，更新和删除操作后在sql-info表中给出相应的提示和执行时间
Create table sql_info(info varchar2(10),time date);

create or replace trigger t2
 after delete or insert or update on xs for each row
declare 
  v_info sql_info.info%type;
begin
  if deleting then
    v_info:='删除'；
  elsif inserting then 
    v_info:='插入'；
  else
    v_info:='更新'；
    
   end if；
   insert into sql_info values(v_info,sysdate);
end t2;

--当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名

create or replace trigger t3
before insert or update or delete on scott.emp
for each row
  
begin
     if inserting then 
       dbms_output.put_line(:new.empno||'---->'||:new.ename);
       
     elsif updating then 
        dbms_output.put_line(:old.sal||'----->'||:new.sal);
        
     else
       dbms_output.put_line(:old.empno||'----->'||:old.ename);
     end if;
end t3


--触发t3
Set serveroutput on
declare 
begin
    update scott.emp set empno=7521 where empno=7522;
  commit;
end;



--针对Scott.emp表，记录其相应操作的信息，具体如下:
--当执行插入操作时，统计操作后员工人数；
--当执行更新工资操作时，统计更新后员工平均工资；
--当执行删除操作时，统计删除后各个部门剩余的人数(游标)。

create or replace trigger t4

after insert or update or delete on scott.emp

declare
   v_1 number;
   v_2 scott.emp.sal%type;
begin
  if inserting then
      select count(*) into v_1 from scott.emp;
      dbms_output.put_line('添加成员后的总人数:'||'---->'||v_1);
  elsif updating then 
    select avg(sal) into v_2 from scott.emp;
    dbms_output.put_line('更新记录后平均工资：'||'----->'||v_2);
   else 
     for v_s in (select deptno, count(*) num from scott.emp group by deptno)
       loop
       dbms_output.put_line('删除记录后各个部门的部门号和总人数：'||v_s.deptno||'<---->'||v_s.num);
       end loop;
    end if;
end t4;



delete from scott.emp where hiredate<=to_date('1980-12-17','yyyy-mm-dd');


update scott.emp set empno=1111 where empno=7934;



Select to_char(sysdate,'yyyy-MM-dd HH24:mi')  from dual;


Select to_char(sysdate, 'DAY')  from dual;

-- 建一触发器，作用为禁止在休息日（周六、周天）改变scott.emp雇员信息（包括添加删除和修改）。
create or replace trigger t6 
  before insert or update or delete
  on scott.emp
  
begin
  if to_char(sysdate,'day') in ('星期五') then
    raise_application_error(-20222,'不能在星期三修改员工信息');
    end if;
end;

update scott.emp set ename='candy'  where empno=7876;