源码-Oracle数据库管理-第十五章-触发器和游标-Part 3(理解触发器)

数据库 来源:hpdlzu80100 61℃ 0评论

一边听着动人的古典音乐,一边敲着代码(SQL脚本也算是代码吧:)),用这种方式迎接新的一年的到来,也算是一种全新的体验吧。奋斗

--代码15.11 为视图创建替代触发器
--创建一个连接emp和dept表的视图
CREATE OR REPLACE VIEW scott.emp_dept (empno,
                                       ename,
                                       job,
                                       mgr,
                                       hiredate,
                                       sal,
                                       comm,
                                       deptno,
                                       dname,
                                       loc
                                      )
AS
   SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,
          emp.comm, emp.deptno, dept.dname, dept.loc
     FROM dept, emp
    WHERE ((dept.deptno = emp.deptno));

select count(*) from emp_dept;   
drop view emp_dept; 
    
--向视图中插入一行记录    
 INSERT INTO scott.emp_dept
   (empno, ename, job, mgr, hiredate, sal, comm, deptno, dname, loc)
 VALUES
   (7000,
    '刘玄德',
    'CLERK',
    NULL,
    TRUNC(SYSDATE),
    5000,
    300,
    87,
    'IT部',
    '四川');
    
--创建一个替代触发器
CREATE OR REPLACE TRIGGER scott.t_dept_emp
  INSTEAD OF INSERT ON scott.emp_dept --在视图emp_dept上创建INSTEAD OF触发器
  REFERENCING NEW AS n --指定谓词别名
  FOR EACH ROW --行级触发器
DECLARE
  v_counter INT; --计数器统计变量
BEGIN
  SELECT COUNT(*) INTO v_counter FROM dept WHERE deptno =:n.deptno; --判断在dept表中是否存在相应的记录   
  IF v_counter >0 --如果已经存在该dept记录
   THEN
    DELETE FROM dept WHERE deptno =:n.deptno; --则先删除该记录
  END IF;
  INSERT INTO dept VALUES (:n.deptno, :n.dname, :n.loc); --向dept表中插入新的部门记录   
  SELECT COUNT(*) --判断emp表中是否存在员工记录
    INTO v_counter
    FROM emp
   WHERE empno = :n.empno;
  IF v_counter >0 --如果已经存在,则先删除员工记录
   THEN
    DELETE FROM emp WHERE empno = :n.empno;
  END IF;
  INSERT INTO emp --则向emp表中插入新员工记录
    (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  VALUES
    (:n.empno,
     :n.ename,
     :n.job,
     :n.mgr,
     :n.hiredate,
     :n.sal,
     :n.comm,
     :n.deptno);
END;
    
--代码15.12 创建UPDATE替代触发器
CREATE OR REPLACE TRIGGER t_dept_emp_update
   INSTEAD OF UPDATE ON emp_dept      --在视图emp_dept上创建INSTEAD OF触发器
   REFERENCING NEW AS n OLD AS o      --指定谓词别名
   FOR EACH ROW                        --行级触发器
DECLARE
   v_counter   INT;                        --计数器统计变量
BEGIN
   SELECT COUNT (*)
     INTO v_counter
     FROM dept
    WHERE deptno = :o.deptno;             --判断在dept表中是否存在相应的记录   
   IF v_counter >0                         --如果存在,则更新dept表
   THEN
      UPDATE dept SET dname=:n.dname,loc=:n.loc WHERE deptno=:o.deptno;
   END IF;
   SELECT COUNT (*)                      --判断emp表中是否存在员工记录
     INTO v_counter
     FROM emp
    WHERE empno = :n.empno;
   IF v_counter > 0                         --如果存在,则更新emp表
   THEN
      UPDATE emp SET ename=:n.ename,job=:n.job,mgr=:n.mgr, hiredate=:n.hiredate,sal=:n.sal,
                   comm=:n.comm, deptno=:n.deptno WHERE empno=:o.empno;        
   END IF;
END;   
  


--代码15.13 创建DELETE替代触发器

CREATE OR REPLACE TRIGGER t_dept_emp_delete
   INSTEAD OF UPDATE ON emp_dept                  --在视图emp_dept上创建INSTEAD OF触发器
   REFERENCING  OLD AS o                          --指定谓词别名
   FOR EACH ROW                                    --行级触发器
BEGIN
   DELETE FROM emp WHERE empno=:o.empno;        --删除emp表
   DELETE FROM dept WHERE deptno=:o.deptno;        --删除dept表
END;   


--代码15.14 t_emp_dept替代触发器完整示例
--可以基于此触发器,实现重要数据表的审计(日志)功能

CREATE OR REPLACE TRIGGER t_emp_dept
   INSTEAD OF UPDATE OR INSERT OR DELETE ON emp_dept   
   REFERENCING NEW AS n OLD AS o      --指定谓词别名
   FOR EACH ROW                         --行级触发器
DECLARE
   v_counter   INT;                         --计数器统计变量
BEGIN
   SELECT COUNT (*)
     INTO v_counter
     FROM dept
    WHERE deptno = :o.deptno;               --判断在dept表中是否存在相应的记录   
   IF v_counter >0                           --如果存在,则更新dept表
   THEN
      CASE                                 --¬根据不同的条件执行不同的操作
      WHEN UPDATING THEN
         UPDATE dept SET dname=:n.dname,loc=:n.loc WHERE deptno=:o.deptno;
      WHEN INSERTING THEN
         INSERT INTO dept VALUES (:n.deptno, :n.dname, :n.loc); 
      WHEN DELETING THEN
         DELETE FROM dept WHERE deptno=:o.deptno;     --删除dept表      
      END CASE;
   END IF;
   SELECT COUNT (*)                        --判断emp表中是否存在员工记录
     INTO v_counter
     FROM emp
    WHERE empno = :n.empno;
   IF v_counter > 0                            --如果存在,则更新emp表
   THEN
      CASE 
      WHEN UPDATING THEN
         UPDATE emp SET ename=:n.ename,job=:n.job,mgr=:n.mgr, hiredate=:n.hiredate,sal=:n.sal,
                   comm=:n.comm, deptno=:n.deptno WHERE empno=:o.empno;    
      WHEN INSERTING THEN
         INSERT INTO emp                     --如果是INSERT语句,则向emp表中插入一行记录
                  (empno, ename, job, mgr, hiredate, sal,
                   comm, deptno
                  )
           VALUES (:n.empno, :n.ename, :n.job, :n.mgr, :n.hiredate, :n.sal,
                   :n.comm, :n.deptno
                  );
      WHEN DELETING THEN                     --如果是DELETE语句,则删除emp表中的记录
         DELETE FROM emp WHERE empno=:o.empno;   
      END CASE;       
   END IF;
END;     

关闭

IT问道推荐

银行贷款频频被拒?
“Dr信用牛牛”让你远离信用污点 国内首家信用健康管理平台免费为你提供信用修复方案