ORACLE - ＜一战通＞ - 触发器

 

目录

一，触发器

  1，触发器是什么？

  2，触发器的组成

  3，创建第一个触发器

二，DDL触发器

  1，什么是DDL触发器

  2，DDL事件及描述

  3，实例

    (1)，禁止某用户的DDL操作

    (2)，实现对数据库对象操作的日志记录

三，DML触发器

  1，DML触发器是什么

  2，DML触发器类型

  3，实例

   (1)，实现数据安全保护（数据的安全性检查）  -  禁止在某条件下操作表数据（某条数据不允许修改）

 （2），实现数据审计

 （3），实现数据完整性（数据确认）

四，INSTEAD OF  触发器

  1，什么是替代触发器？为什么使用替代触发器？

  2，实例

五，系统触发器

  1，系统触发器是什么？

  2，实例

---

 

一，触发器

  1，触发器是什么？

         只是存放在数据库中，并且被隐含执行的存储过程。

         当发生特定事件时，会自动执行触发器的相应代码。

 

  2，触发器的组成

         触发事件：在何种情况下触发TRIGGER

         触发时间：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后(AFTER)触发

         触发器本身：即该TRIGGER被触发之后的目的和意图，正式触发器本身要做的事情

         触发频率：说明触发器内定义的动作被执行的次数

 

  3，创建第一个触发器

   做ddl删除操作后后，有删除提示

-- 当我们对TEXT_TABLE执行删除操作之后，它就会出现一个提示信息

-- CREATE OR REPLACE 表示创建或覆盖  TRIGGER  触发器关键字

-- AFTER 表示在事件之后    DELETE  表示删除操作

-- BEGIN   END;  之间是触发器需要完成的内容

CREATE OR REPLACE TRIGGER TEXT_TABLE_DEL_A

AFTER DELETE  ON TEXT_TABLE

BEGIN

    DBMS_OUTPUT.put_line('这是TEXT_TABLE_DEL_A的删除的操作！');

END;

-- Create table
create table JIN.TEXT_TABLE
(
  id   VARCHAR2(40),
  name VARCHAR2(40)
);

INSERT INTO TEXT_TABLE(ID,NAME) VALUES('1','1');

DELETE FROM TEXT_TABLE WHERE ID ='1';

效果

 

 

二，DDL触发器

  1，什么是DDL触发器

       创建，修改或者删除 数据库对象 时，会引起相应的触发器操作时间，而此时就可以利用触发器对这些数据库对象的DDL操作进行监控。

触发器语法

CREATE[OR REPLACE]  TRIGGER  触发器的名称

[BEFOR|AFTER|INTEAD OF][DDL事件]  ON  [DATABASE|SCHEMA]

[WHEN触发条件]

[DECLARE]

   [程序的声明部分;变量的声明]

BEGIN

     程序的代码部分

END;

 

  2，DDL事件及描述

序号	DDL事件	触发时机	描述
1	ALTER	B/A	修改对象的结构时触发
2	ANALYZE	B/A	分析数据库对象时触发
3	ASSOCIATE STATISTICS	B/A	启动统计数据库对象时触发
4	AUDIT	B/A	开启审核数据库对象时触发
5	COMMENT	B/A	对数据库对象做注释时触发
6	CREATE	B/A	创建数据库对象时触发
7	DDL	B/A	针对出现的所用DDL时间都会触发
8	DISASSOCIATE STATISTICS	B/A	关闭统计数据库对象时触发
9	DROP	B/A	删除数据库对象时触发
10	GRANT	B/A	通过SQL的GRANT命令赋权时触发
11	NOAUDIT	B/A	禁用审核数据库对象时触发
12	RENAME	B/A	通过SQL的RENAME命令是对对象重命名时触发
13	REVOKE	B/A	通过SQL的REVOKE语句撤销授权时触发
14	TRUNCATE	B/A	通过SQL的TRUNCATE语句截断表时触发

 

  3，实例

    (1)，禁止某用户的DDL操作

CREATE OR REPLACE TRIGGER jin_trigger

BEFORE DDL  ON JIN.SCHEMA

BEGIN

   RAISE_APPLICATION_ERROR(-20005,'jin用户禁止所有的DDL操作！');

END;

--删除

-- DROP trigger  jin_trigger ; 

-- 用jin用户登录测试

CREATE SEQUENCE test;

DELETE SEQUENCE test;


--描述
RAISE_APPLICATION_ERROR 是用来测试的异常处理。

是将应用程序转悠的错误从服务端转达到客户端应用程序（其他机器上的SQLPLUS或者其他前台开发语言）。

RAISE_APPLICATION_ERROR(error_number_in IN NUMBER,error_msg_in IN VARCHAR2);

err_number_in  只允许-20000到-20999之间

error_msg_in   长度不能超过2K

 

    (2)，实现对数据库对象操作的日志记录

--创建一个记录数据库对象DDL操作的日志表

create table object_log(

       logid varchar2(32) primary key,       

       operate_date date not null，

       operate_type varchar2(64) not null,

       operate_owner varchar2(64) not null,

       remarks varchar2(4000) not null

);


--创建触发实现对数据库对象DDL操作记录的触发器

CREATE OR REPLACE TRIGGER object_trigger

AFTER CREATE OR DROP OR ALTER

ON DATABASE

BEGIN

  INSERT INTO object_log(logid,operate_date,operate_type,operate_owner,remarks)

  VALUES(sys_guid,sysdate,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_OWNER,

  'ora_dict_obj_name  : '||ora_dict_obj_name||' ora_sysevent :  '||ora_sysevent);

END;


-- 切换用户jin 测试 

create SEQUENCE test_seq;

DROP  SEQUENCE test_seq;

-- 切换用户sys测试 

select * from object_log;

效果

 

三，DML触发器

  1，DML触发器是什么

       是指机遇DML操作所建立的触发器。可用于实现数据安全保护，数据审计，数据完整性，参照完整性，数据复制等功能；

CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE|AFTER]  {DELETE|INSERT|UPDATE[OF列表]}
ON tableName
[FOR EACH ROW [WHEN(条件)]]  --行级触发器

PLSQL块

  2，DML触发器类型

       语句触发器：在指定的操作语句之前或之后执行一次，不管整条语句影响了多少行。

       行触发器：触发语句作用的没一条记录都被触发，在行触发器中使用:old和:new伪记录变量，识别值的装填。:old操作该行之前这行的值，:new操作该行之后这行的值。

 

 

  3，实例

       触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。
如果指定为for each row，即为行级触发器，则触发事件每次作用于一个记录，触发器就会执行一次。
:old和:new标识符就需要指定for each row,否则会出现ORA-04082: NEW 或 OLD 引用不允许在表级触发器中的问题！

   (1)，实现数据安全保护（数据的安全性检查）  -  禁止在某条件下操作表数据（某条数据不允许修改）

CREATE OR REPLACE TRIGGER TEXT_TABLE_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
ON TEXT_TABLE
BEGIN
  IF  to_number(to_char(sysdate,'hh24'))>10 THEN
       RAISE_APPLICATION_ERROR(-20006,'不能在10点以后修改TEXT_TABLE表数据！');
  END IF;
END;

效果

 （2），实现数据审计

          记录TEXT_TABLE 数据的变化（增删改），查询虽然也能实现，但是感觉没有意义。还可以实现参照完整性（级联更新）

--记录表
CREATE TABLE TEXT_TABLE_LOG(
				ID VARCHAR2(40),
				OPERATE_ID VARCHAR2(40),
        OPERATE_TYPE VARCHAR2(8),
				OPERATE_DATE  DATE
);
--触发器
CREATE OR REPLACE TRIGGER TEXT_TABLE_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
ON TEXT_TABLE
FOR EACH ROW   -- 这个必须加，否则会出现ORA-04082
DECLARE     -- 声明变量
       OPERATE_TYPE varchar2(8):='edit';
       TEXT_TABLE_ID VARCHAR2(40);
BEGIN
   TEXT_TABLE_ID := :old.id;
   IF    ((NVL(:old.ID,1)=1  and NVL(:new.ID,1)!=1 )) THEN
         OPERATE_TYPE  := 'add';
              TEXT_TABLE_ID := :new.id;
   ELSIF (NVL(:new.ID,1)=1  and NVL(:old.ID,1)!=1)  THEN
         OPERATE_TYPE := 'del';
   END IF;
     
   --插入语句
   INSERT INTO TEXT_TABLE_LOG VALUES(SYS_GUID(),TEXT_TABLE_ID,OPERATE_TYPE,SYSDATE);

END;

INSERT INTO TEXT_TABLE(ID,NAME) VALUES('1','1');
UPDATE TEXT_TABLE SET  NAME ='2'  WHERE ID ='1';
DELETE FROM  TEXT_TABLE WHERE ID='1';
SELECT *  FROM TEXT_TABLE_LOG   ;

 

效果

​

 

 （3），实现数据完整性（数据确认）

        首选约束，如果是约束无法实现的复杂逻辑，使用行级触发器。同1，判断有所变化。old和new在代码块中需要加：，在WHEN中不需要。

CREATE OR REPLACE TRIGGER TEXT_TABLE_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON TEXT_TABLE
FOR EACH ROW 
WHEN(old.id='1')
BEGIN
   
     RAISE_APPLICATION_ERROR(-20006,'根节点不允许修改--WHEN');
END;

  效果 

 

四，INSTEAD OF  触发器

 

  1，什么是替代触发器？为什么使用替代触发器？

      在简单的视图上可以执行INSERT,UPDATE,DELETE操作，但在复杂视图上是有限制的。为了执行DML操作，需要建立替代触发器。复杂视图（子查询包含有，分组函数，distinct,连接查询，集合运算符。禁止DML操作 ）

     限制：

                只适用于视图；

               不能指定BEFORE和AFTER选项；

               不能再具有WITH  CHECK OPTION选项上建立；

                必须包含 FOR EACH ROW 选项。

 

  2，实例
 

--创建视图
CREATE OR REPLACE VIEW V_TEXT_TABLE_LOG   AS
select tt.id,tt.name,ttl.operate_type,ttl.operate_date 
from TEXT_TABLE tt  left join   TEXT_TABLE_LOG ttl  on tt.id = ttl.operate_id

--替代触发器
CREATE OR REPLACE TRIGGER instead_V_TEXT_TABLE_LOG
INSTEAD OF INSERT ON V_TEXT_TABLE_LOG
FOR EACH ROW
DECLARE
    v_temp INT;
BEGIN
    SELECT COUNT(*) INTO v_temp FROM TEXT_TABLE WHERE ID = :new.ID;
    IF v_temp = 0 THEN
       INSERT INTO TEXT_TABLE VALUES(:new.ID,:new.name);
    END IF;
    SELECT COUNT(*) INTO v_temp FROM TEXT_TABLE_LOG WHERE OPERATE_ID  = :new.ID;
    IF v_temp = 0 THEN
       INSERT INTO TEXT_TABLE_LOG VALUES(SYS_GUID(),:new.ID,:new.operate_type,:new.operate_date);
    END IF;
END;

INSERT INTO V_TEXT_TABLE_LOG VALUES('4','4','V_add',sysdate);
select *  from V_TEXT_TABLE_LOG;

效果 未添加，无法进行更新操作     

  添加后，顺利新增成功

五，系统触发器

 

  1，系统触发器是什么？

       系统触发器是由特定系统事件锁触发的触发器。系统事件是指与例程或方案相关的数据库事件，它包含STARTUP，SHUTDOWN，DB_ROLE_CHANGE和SERVERROR的四种事件。需要有系统用户SYS操作。

    事件触发器

        TARTUP ：事件触发器在启动数据库后触发

        SHUTDOWN：事件触发器在关闭数据库之前触发

        DB_ROLE_CHANGE：事件触发器在改变角色之后第一次打开数据库时触发

        SERVERROR：事件触发器在发生Oracle错误时触发

    限制

      SHUTDOWN       不能 AFTER
      STARTUP   SERVERERROR   DB_ROLE_CHANGE   不能 BEFORE
 

  2，实例

--创建一个AFTER系统触发器
create or replace trigger end_event_trigger
AFTER STARTUP   OR DB_ROLE_CHANGE  OR SERVERERROR on database
begin
  insert into event_table values(ora_sysevent,SYSDATE);
end;

--创建一个BEFORE系统触发器
create or replace trigger start_event_trigger
BEFORE   SHUTDOWN    on database
begin
  insert into event_table values(ora_sysevent,SYSDATE);
end;
 
--查看
SELECT    *   FROM event_table;

效果

第一次关闭时，按Ctrl+C关闭窗口，然后重启ORACLE服务和监听。再次进行关闭，开启。

​

​