数据库 触发器

触发器

---

1. 创建一个emp1表的插入或修改触发器，功能是：员工的工资不能超过自己的经理的工资，超过则报错误。

   create or replace trigger obj5_1
   	before insert or update on emp1 for each row;
   	declare
   	v emp1.sal%type;
   	 PRAGMA AUTONOMOUS_TRANSACTION;
   	 begin
     select sal into v from emp1 where deptno= :new.deptno and job='manager'
   	 case
   	 	when INSERTING then
        if :new.sal>v_sa 
         then
           raise_application_error (-20001,'员工工资不能高于自己的经理工资');
       end if;
       when UPDATING then
       if :new.sal>v_sa then
       raise_application_error (-20001,'员工工资不能高于自己的经理工资');
       end if;
       end case;
   end;
   

2. 创建一个名为dept_summary(deptno,emp_count,sal_sum)的表，保存每一个部门的人数与工资总额。创建一个触发器，当对emp2表执行INSERT, UPDATE, DELETE 操作时，自动更新dept_summary 表中的数据。

   create or replace trigger obj5_2
   after
    insert or update or delete on emp2;
    declare
    	cursor obj5_2emp2 is select deptno,count(empno) as emp_count,sum(sal) as sal_sum 
       from emp2 group by deptno;
       begin
       	delete dept_summary;
       	for obj5_2emp2 in obj5_2emp2 loop
     dbms_output.put_line(obj5_emp2.deptno || obj5_emp2.emp_count || obj5_emp2.sal_sum);
     insert into dept_summary values(obj5_emp2.deptno,obj5_emp2.emp_count,obj5_emp2.sal_sum);
     end loop;
   end;
   

3. 创建一个dept1表的触发器，当删除一个部门时，同时也在emp1表中删除该部门所有的员工记录。

   create or replace trigger obj5_3
   before delete on emp1 for each row;
   declare
   
   begin
   	delete from emp1 where deptno=:old.deptno;
   end;
   

4. 创建一个empdept 视图的instead of触发器。当向该视图插入一条记录时，先判断该部门是否存在，若存在，则向emp2表插入一条相应的员工记录；若不存在该部门，则先向dept1表插入一条相应的部门记录，再向emp2表插入一条相应的员工记录。当从该视图删除记录时，则从emp2表中删除相应的员工记录。

   create or replace 
   trigger obj5_5
       before INSERT OR UPDATE OR DELETE on emp3 for each row
   declare
       vd number;
       vb number;
   begin
       if inserting then
           select count(deptno) into vd from dept where deptno=:new.deptno;
           IF vd=0 THEN
               raise_application_error(-20000,'部门不存在');
               --dbms_output.put_line('部门号不存在不能更新');
           END IF;
       ELSIF updating THEN
       SELECT COUNT(deptno) INTO vd FROM dept1 WHERE deptno=:NEW.deptno;
       IF vd =0 THEN
          dbms_output.put_line('部门号不存在不能更新');--raise_application_error(-20001,'部门号不存在不能更新');
       END IF;
    -- UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno;
     
       ELSIF deleting THEN
      --判部门号是否存在
       SELECT COUNT(deptno) INTO vd  FROM dept1 WHERE deptno=:old.deptno;
        --如果部门号不存在
        IF vd =0 THEN
        raise_application_error(-20002,'部门号不存在不能删除');
       END IF;
     ELSE
       NULL;
     END IF; 
   end;
   
   

5. 创建一个emp3表的触发器，功能是：当插入记录时，若dept表不存在相应的部门，则拒绝插入；当修改记录中部门号deptno时，若dept1表不存在相应的部门，则拒绝修改。也就是不定义外键，用触发器完成相应的约束。

   create or replace 
   trigger obj5_6
   before insert or update on emp4 for each row
   begin
   :new.ename:=upper(:new.ename);
   end;
   
   

6. 创建一个emp4表的触发器，功能是：当插入或修改记录时，将员工的姓名转换成大写字母。

   create or replace 
   trigger obj5_6
   before insert or update on emp4 for each row
   begin
   :new.ename:=upper(:new.ename);
   end;