Oracle中如何使用触发器

分类：编程技术时间：2024-02-20 15:25 浏览：0 评论：0

Oracle中如何使用触发器？针对这个问题，本文详细介绍了相应的分析和解答，希望能够帮助更多想要解决这个问题的朋友找到更简单、更容易的方法。

触发器类型

触发器作为独立对象存储在数据库中。它们与存储过程和函数不同，存储过程和函数需要用户显式调用才能执行，而触发器则由事件启动。也就是说，当事件发生时，触发器会自动隐式运行。此外，触发器无法接收参数。因此运行触发器称为触发或触发。 ORACLE 事件是指对数据库表的 INSERT、UPDATE 和 DELETE 操作或对视图的类似操作。 ORACLE扩展了触发器的功能，可以触发ORACLE，比如数据库的启动和关闭。因此，触发器常常用来完成复杂业务规则的约束at都很难通过数据库的完整性约束来完成，或者监控对数据库的各种操作来实现审计功能。

开发中肯定会用到Oracle触发器，本文将详细讲解。

本例中主要用到的是Oracle中scott用户下的emp和dept表。数据如下

1.触发器的概念

1．概念：

触发器的本质是存储过程。顾名思义，Oracle会在特定事件发生时执行触发器中的步骤。代码。它的组成部分可以分为3部分：第一部分是触发器执行的条件，即触发触发器时的事件。第二部分是触发器在什么时间点执行，即触发器的发生，比如之前和之后。触发器本身要做的第三部分是触发器触发后要表达的具体事件，即begin和end之间的sql。

2.触发器分类：

1. DDL触发器：执行DDL操作后触发的事件。

常用的ddl操作包括：grant（授权）、revoke（撤销授权）、create（创建）、drop（删除）、alter（修改）、comment（评论）、audit（ Review）、重命名（rename）在进行具体例子之前，我们先解释一下另一个概念：oracle中的user和schema：

user：oracle中的用户，拥有数据库对象和添加数据库对象。删除、修改和检查权限。 schema：应该使用户下所有数据库对象的集合。类似于生活中房屋图式与房屋主人的关系。你是用户，你可以通过alter session查看别人的房子，但是你能改变房子里的家具吗？，这取决于什么除非您是所有房屋中拥有最高权限的 DBA，否则该房屋的所有者授予您此权限。

ddl示例：禁止scott用户的所有ddl操作

CREATE OR REPLACE TRIGGER scott_triggerBEFORE DDLON SCHEMABEGIN RAISE_APPLICATION_ERROR(-20008,'禁止scott用户的所有ddl操作scott user ddl操作');END;

创建序列myseq;

这里可以看到创建后触发器，如果你还使用ddl操作的话，就会报错。

2. DML触发器：基于DML操作的触发器，可分为行触发器和语句触发器。

A.语句触发器：DML操作可能影响很多行，主要用于数据安全保护。

示例：周四、周五禁止修改emp表数据

CREATE OR REPLACE TRIGGER emp_triggerBEFORE UPDATE OR DELETE OR INSERTON empBEGIN IF to_char(sysdate ,'day') IN ('星期四','星期五') THEN RAISE_APPLICATION_ERROR(-20008,'星期四和星期五不允许修改 emp 表'); END IF;END;

updateemp set sal=800;

这里创建触发器后，会出现触发器错误当你想改变每个人的工资时就会发生。每个人的工资意味着影响很多线路。

B.行级触发器：对于需要操作的行，有关键字：对于每一行，用于

(1)实现数据审计功能：

示例：Make一个记录已删除员工信息的表记录已删除员工信息

为了不改变Oracle中emp表的数据，新建一个emp_new表

创建表 emp_newasselect * from emp;

创建表 emp_audit(name varchar2(10),delete_time Date);

创建或替换触发器delete_triggerAFTER DELETE ON emp_newF或者每个行开始插入 emp_audit value(:old.ename,sysdate);END;

从 emp_new 中删除，其中 empno='7499';

select * from emp_audit;

这里可以看到如何创建触发器的时候，使用了for every row关键字，:old.* **用于表示上表中更改后的数据，:new.***用于表示更改后的数据。删除数据后，会有相应的记录。

（2）实现数据完整性：

例：要求员工加薪后，不能低于原来的工资，而且增加的工资也不能更高比原来的50%。

为了不改变Oracle中emp表的数据，新建了一张emp_new表

create table emp_newasselect * from emp;< /pre>在 emp_new 上更新 Sal 之前创建或替换触发器 emp_triggerFOR EACH ROWWHEN (new.sal1.5*old.sal)BEGIN RAISE_APPLICATION_ERROR(-20008,'工资只涨不降，涨幅不能大于50%');END;
< pre class="brush:sql;">update emp_new set sal = 1.6*sal where empno='7788';

这里可以看到会触发一个trigger错误当数据改变时。要修改表中的某个字段，只需使用 UPDATE OF 即可。另外，如果new和old在PLSQL块之外，即BEGIN之外，则不可以加冒号。

（3）参照完整性：

示例：主要用于级联更新。例如，当dept表中的deptno更新时，emp表中的deptno也会更新。

这里我们还是新建两个表，数据与emp表和dept表相同。

从 emp 创建表 emp_newasselect *

从 dept 创建表 dept_newasselect *

创建或替换触发器cascade_triggerAFTER UPDATE OF deptno ON dept_newFOR EACH ROWBEGIN UPDATE emp_new SET deptno=:new.deptno WHERE deptno=:old.deptno;END;

update dept_new set deptno =15 where deptno =20;

从 dept_new 选择 *;

从 emp_new 选择 *;

这里的full new指的是多张具有主从关系的表。当更新主表主键时，需要更新从表的相关数据。

3.替代触发器：

我们先来说另一个概念：带检查选项的视图：

If 如果视图的定义包含条件（例如 where 子句）并且应用于视图的任何 INSERT 或 UPDATE 语句都应包含该条件，视图必须使用WITH CHECK OPTION 进行定义。

示例：

CREATE VIEW emp_view(ename,empno)AS SELECT ename,empno FROM emp WHERE deptno=20WITH CHECK OPTION;

这里有一个条件，部门号是20，所以任何修改这个视图的语句都必须针对20号部门的员工。

继续替换触发器的概念：关键字而不是，主要针对一些复杂视图，因为级联表生成的视图没有update、insert、delete等关键字可以使用，但是没有before、after等关键字，无法建立在带check的视图上选项。例如，如果您创建了 emp 表和 dept 表的级联视图，则无法将其添加到其中。添加数据，现在通过触发器解决：

示例：

仍然新建两个表，数据与emp表和dept表相同。

创建表 emp_newASSELECT * FROM emp;创建表 dept_newASSELECT * FROM dept;

创建视图 emp_deptASSELECT d.deptno ,d.dname,e.empno,e.enameFROM dept_new d,emp_new eWHERE d.deptno=e.deptno;

他re，用户 scott 需要获得 sysdba 的授权才能创建视图：

grant create view to scott;

创建或替换触发器而不是_triggerINSTEAD OF INSERT ON emp_deptFOR EACH ROWDECLARE v_temp INT;BEGIN SELECT COUNT(*) INTO v_temp FROM dept_new WHERE deptno=:new.deptno; IF v_temp=0 THEN INSERT INTO dept_new(deptno,dname) VALUES(:new.deptno,:new.dname);万一; SELECT COUNT(*) INTO v_temp FROM emp_new WHERE empno=:new.empno; IF v_temp=0 THEN INSERT INTO emp_new(deptno,empno, ename) VALUES(:new.deptno,:new.empno,:new.ename); END IF;END;

插入 emp_dept 值(15,'HUMANRESOURCE',7999,'LEAF');

select * from emp_new;

select * from dept_new;

何时这里在触发器中插入视图，对应的emp_new和dept_new会被修改，从而修改复杂视图。

4.系统触发：如顾名思义，系统触发器触发的事件，常用的系统事件startup、shutdown、db_roll_change、服务器错误等。

示例：记录启动数据库时的事件和时间。

因为这是系统触发器，所以需要使用sysdba权限登录。

创建表 event_table(event VARCHAR2(50),event_time DATE);

启动后创建或替换触发器 event_trigger ON DATABASEBEGIN INSERT INTO event_table VALUES(ora_sysevent,sysdate);END;

从 event_table 选择 *;

pre>

3.触发器综合示例

示例：制作日志记录用户scott的一些操作：

首先在sysdba权限下创建日志表、序列和触发器：

创建表 object_log(logid NUMBER CONSTRAINT pk_logid PRIMARY KEY,operatedate DATE NOT NULL,objecttype VARCHAR2(50) NOT NULL,object所有者 VARCHAR2(50) NOT NULL);

创建序列 obj_log_seq;

创建或替换触发器 object_triggerAFTER CREATE OR DROP OR ALTER ON DATABASEBEGIN INSERT INTO object_log VALUES(obj_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner);END;

在用户 scott 下随意创建一些内容：