源码-PL/SQL从入门到精通-第十五章-触发器-Part 2-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/52035359
触发器的功能主要有：
1. 完成表的变更校验（当表的数据发生增、删、改时，提供验证逻辑）
2. 自动数据库维护
3.控制数据库管理活动
通过调试代码，可以增强对各知识点的理解。
--代码15.14 在触发器中使用自治事务
CREATE OR REPLACE TRIGGER t_emp_comm
   BEFORE UPDATE ON emp     --触发器作用的表对象以及触发的条件和触发的动作
   FOR EACH ROW             --行级别的触发器
   WHEN(NEW.comm>OLD.comm)    --触发器条件
DECLARE   
   v_comm   NUMBER;          --语句块的声明区
   PRAGMA AUTONOMOUS_TRANSACTION; --自治事务      
BEGIN
   IF UPDATING ('comm') THEN --使用条件谓词判断是否是comm列被更新
      v_comm := :NEW.comm - :OLD.comm; --记录工资的差异
      DELETE FROM emp_history 
            WHERE empno = :OLD.empno;      --删除emp_history中旧表记录
      INSERT INTO emp_history              --向表中插入新的记录
           VALUES (:OLD.empno, :OLD.ename, :OLD.job, :OLD.mgr, :OLD.hiredate,
                   :OLD.sal, :OLD.comm, :OLD.deptno);
      UPDATE emp_history                   --更新薪资值
         SET comm = v_comm
       WHERE empno = :NEW.empno;
   END IF;
   COMMIT;                                 --提交结束自治事务
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;                            --发生任何意外回滚自治事务
END;

select * from emp_history;
truncate table emp_history;
update emp set comm=comm*1.2 where deptno=20;
rollback;

SELECT * FROM emp WHERE deptno=20;

--15.3.2 定义替代触发器
--创建视图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));
    
    
--实现替代触发器（发现的问题：触发器执行插入操作时，主键重复时，未报错）
CREATE OR REPLACE TRIGGER t_dept_emp
   INSTEAD OF INSERT ON 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
      INSERT INTO dept VALUES (:n.deptno, :n.dname, :n.loc);   --向dept表中插入新的部门记录
   END IF;
   SELECT COUNT (*)                   --判断emp表中是否存在员工记录
     INTO v_counter
     FROM emp
    WHERE empno = :n.empno;
   IF v_counter = 0                   --如果不存在，则向emp表中插入员工记录
   THEN
      INSERT INTO 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 IF;
END;


SELECT * FROM dept;

SELECT * FROM emp_dept;

INSERT INTO emp_dept
            (empno, ename, job, mgr, hiredate, sal, comm, deptno,
             dname, loc
            )
     VALUES (8001, '龙太郎', '神职', NULL, TRUNC (SYSDATE), 5000, 300, 82,
             '神庙', '龙山'
            );
            
--代码15.16 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.17 Delete替代触发器
CREATE OR REPLACE TRIGGER t_dept_emp_delete
   INSTEAD OF DELETE 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;   


SELECT * FROM emp_dept WHERE empno=8000;

DELETE FROM emp_dept WHERE empno=8000;


--代码15.18 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
                  (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 FROM emp WHERE empno=:o.empno;   
      END CASE;       
   END IF;
END;            


SELECT * FROM emp;

--代码15.19 创建嵌套表视图
--创建用于嵌套表的对象类型
CREATE OR REPLACE TYPE emp_obj AS OBJECT(
   empno NUMBER(4),
   ename VARCHAR2(10),
   job VARCHAR2(10),
   mgr NUMBER(4),
   hiredate DATE,
   sal NUMBER(11,2),
   comm NUMBER(7,2),
   deptno NUMBER(2)
);
--创建嵌套表类型
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_obj;
--创建嵌套表视图，MULTISET必须与CAST一起使用
CREATE OR REPLACE VIEW dept_emp_view AS
   SELECT deptno,dname,loc,
   CAST(MULTISET(SELECT * FROM emp WHERE deptno=dept.deptno) AS emp_tab_type) emplst
   FROM dept;

drop view  dept_emp_view;  
drop type emp_tab_type;
drop type emp_obj;
SELECT * FROM dept_emp_view  where deptno=10;

select * from emp;

--不能在嵌套表视图列中执行DML
INSERT INTO TABLE (SELECT emplst
                     FROM dept_emp_view
                    WHERE deptno = 10)
     VALUES (8003, '四爷', '皇上', NULL, SYSDATE, 5000, 500, 10);
     
-- 代码15.20 创建嵌套表替代触发器    
CREATE OR REPLACE TRIGGER dept_emp_innerview
   INSTEAD OF INSERT
   ON NESTED TABLE emplst OF dept_emp_view                       --创建嵌套表替代触发器
BEGIN
   INSERT INTO emp                                             --插入子表记录
               (deptno, empno, ename, job, mgr,
                hiredate, sal, comm
               )
        VALUES (:PARENT.deptno, :NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr,
                :NEW.hiredate, :NEW.sal, :NEW.comm
               );
END;

--利用替代触发器可在嵌套表视图列中执行DML
INSERT INTO TABLE (SELECT emplst
                     FROM dept_emp_view
                    WHERE deptno = 10)
     VALUES (8003, '四爷', '皇上', NULL, SYSDATE, 5000, 500, 10);
     

--15.4 系统事件触发器     
DROP TABLE created_log

--代码15.21 系统触发器创建示例
CREATE TABLE created_log
(
    obj_owner VARCHAR2(30),   --所有者
    obj_name  VARCHAR2(30),   --对象名称
    obj_type  VARCHAR2(20),   --对象类型
    obj_user VARCHAR2(30),    --创建用户
    created_date DATE     --创建日期
)

--使用system会话
CREATE OR REPLACE TRIGGER t_created_log
   AFTER CREATE ON scott.SCHEMA                  --在soctt方案下创建对象后触发
BEGIN
   --插入日志记录
   INSERT INTO scott.created_log(obj_owner, obj_name,
                obj_type, obj_user, created_date
               )
        VALUES (SYS.dictionary_obj_owner, SYS.dictionary_obj_name,
                SYS.dictionary_obj_type, SYS.login_user, SYSDATE
               );
END;

create table test (id int, name varchar2(100));
drop table test;
select * from created_log;
truncate table created_log;

DROP TRIGGER t_created_log;


--代码15.22 Logon系统触发器创建示例
--以DBA身份登录，创建下面的登录记录表
CREATE TABLE log_db_table
(
   username VARCHAR2(20),
   logon_time DATE,
   logoff_time DATE,
   address VARCHAR2(20) 
);

--以soctt身份登录，创建下面的登录记录表
CREATE TABLE log_user_table
(
   username VARCHAR2(20),
   logon_time DATE,
   logoff_time DATE,
   address VARCHAR2(20) 
);

--以DBA身份登录，创建DATABASE级别的LOGON事件触发器
CREATE OR REPLACE TRIGGER t_db_logon
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO log_db_table(username,logon_time,address)
              VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;

--以scott身份登录，创建如下的SCHEMA级别的LOGON事件触发器
CREATE OR REPLACE TRIGGER t_user_logon
AFTER LOGON ON SCHEMA
BEGIN
  INSERT INTO log_user_table(username,logon_time,address)
              VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;

select * from log_user_table;

--代码15.23 Startup和Shutdown触发器
--以DBA身份进入系统，创建临时表
CREATE TABLE event_table(
   sys_event VARCHAR2(30),
   event_time DATE
);

--在DBA级别创建如下的2个触发器
CREATE OR REPLACE TRIGGER t_startup
AFTER STARTUP ON DATABASE       --STARTUP只能是AFTER
BEGIN
   INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER t_startup
BEFORE SHUTDOWN ON DATABASE       --SHUTDOWN只能是BEFORE
BEGIN
   INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/


--代码15.24 使用ora_alter_column禁止非法更改列
CREATE OR REPLACE TRIGGER preserve_app_cols
   AFTER ALTER ON SCHEMA
DECLARE
   --获取一个表中所有列的游标
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
   -- 如果正使用的是ALTER TABLE语句修改表
   IF ora_dict_obj_type = 'TABLE'
   THEN
      -- 循环表中的每一列
      FOR v_column_rec IN curs_get_columns (
                             ora_dict_obj_owner,
                             ora_dict_obj_name
                          )
      LOOP
         --判断当前的列名正在被修改
         IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name)
         THEN
            IF v_column_rec.column_name='EMPNO' THEN
               RAISE_APPLICATION_ERROR (
                  -20003,
                  '不能对empno字段进行修改'
               );
            END IF; 
         END IF; 
      END LOOP;
   END IF;
END;


ALTER TABLE scott.emp MODIFY(empno NUMBER(8))

ALTER TABLE scott.emp MODIFY(ename varchar2(22)) 

--代码15.25 使用After servererror触发器记录错误日志
--错误日志记录表
CREATE TABLE servererror_log(
   error_time DATE,
   username  VARCHAR2(30),
   instance NUMBER,
   db_name VARCHAR2(50),
   error_stack VARCHAR2(2000)
);
--创建错误触发器，在出现数据库错误时触发。
CREATE OR REPLACE TRIGGER t_logerrors
   AFTER SERVERERROR ON DATABASE
BEGIN
   INSERT INTO servererror_log
        VALUES (SYSDATE, login_user, instance_num, database_name,
                DBMS_UTILITY.format_error_stack);
END;


--查询用户权限(使用sysdba权限)
select privilege from dba_sys_privs where grantee='SCOTT'  
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT'  )