这篇文章主要为大家详细介绍了Oracle触发器trigger,具有一定的参考价值，感兴趣的小伙伴们可以参考一下

触发器的相关概念和语法供您参考。具体情况如下

摘要

这篇博文主要讨论了以下内容:

什么是触发器
触发器的应用场景
触发器的语法
触发器的类型

案例

数据:

触发器和第一触发器的概念

数据库触发器是与表相关联的存储PL/SQL语句。

每当对指定的表发出特定的数据操作语句(insert update delete)时，Oracle都会自动执行触发器中定义的语句序列。

举个简单的例子:

当一条新记录添加到员工表中时，将自动打印“新员工成功插入”。

create or replace trigger insertStaffHint after insert on xgj_test for each rowdeclare -- local variables herebegin dbms_output.put_line('新增员工成功');end insertStaffHint;

触发器的应用场景

复杂的安全检查
数据确认
数据库审计
数据备份和审计

触发器的语法

CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER }{INSERT | DELETE | UPDATE [OF column [, column …]]}[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]ON [schema.]table_name | [schema.]view_name[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}][FOR EACH ROW ][WHEN condition]PL/SQL_BLOCK | CALL procedure_name;

其中包括:

BEFORE和AFTER分别指出触发器的触发顺序是前触发和后触发。前触发器在触发器事件执行前触发当前创建的触发器，后触发器在触发器事件执行后触发当前创建的触发器。

FOR EACH ROW选项指示触发器是行触发器。

行触发器和语句触发器的区别如下:当一条DML语句遍历数据库中的多行数据时，只要满足触发器约束，每个数据行都要激活一次触发器；语句触发器将整个语句操作作为一个触发事件，当满足约束条件时，触发器被激活一次。
省略FOR EACH ROW选项时，BEFORE和AFTER触发器是语句触发器，而INSTEAD OF触发器只能是行触发器
。

REFERENCING子句描述了相关的名称，这些名称可用于引用PL/SQL块和行触发器的WHEN子句中的当前新列值和旧列值。默认的相关名称分别是旧名称和新名称。在触发器的PL/SQL块中应用相关名称时，必须在它们前面添加冒号(:)，但不能在WHEN子句中添加冒号。

WHEN子句描述触发器约束。当条件是逻辑表达式时，它必须包含相关名称，但不能包含查询语句或PL/SQL函数。WHEN子句指定的触发器约束只能用于BEFORE和AFTER行触发器，而不能用于INSTEAD OF行触发器和其他类型的触发器。

当一个基表被修改(插入、更新、删除)时，存储过程会根据它所附加的基表的变化而自动触发，所以与应用程序无关。使用数据库触发器可以确保数据的一致性和完整性。

每个表最多可以创建12种类型的触发器:

BEFORE INSERTBEFORE INSERT FOR EACH ROWAFTER INSERTAFTER INSERT FOR EACH ROWBEFORE UPDATEBEFORE UPDATE FOR EACH ROWAFTER UPDATEAFTER UPDATE FOR EACH ROWBEFORE DELETEBEFORE DELETE FOR EACH ROWAFTER DELETEAFTER DELETE FOR EACH ROW

触发器的类型。

Row trigger要求当一个DML语句操作影响数据库中的多行数据时，只要满足触发器约束条件，就为每个数据行激活一次触发器；在行级触发器中，使用:old和:new伪记录变量来标识值的状态。

触发器将整个语句操作作为一个触发事件，当满足约束条件时，触发器被激活一次。

省略FOR EACH ROW选项时，BEFORE和AFTER触发器是语句触发器，
但INSTEAD OF触发器只能是行触发器。

其他规则

触发器名称不同于过程名称和包名称。它是一个单独的名称空，因此触发器名称可以与表或过程同名，但在模式中不能相同。

DML触发器的限制:

CREATE TRIGGER语句文本的字符长度不能超过32KB；
触发器体中的SELECT语句只能是SELECT … INTO …结构或用于定义游标的SELECT语句。
在触发器中不能使用数据库事务控制语句COMMITROLLBACK，SVAEPOINT语句；
触发器调用的过程或函数也不能使用数据库事务控制语句；
在触发器中不能使用LONG、LONG RAW类型。
LOB列的列值可以在触发器中被引用，但是LOB列中的数据不能被修改:NEW

DML触发器的要点:

触发时间:指定触发器的触发时间。如果指定了BEFORE，则表示在执行DML操作之前触发，以防止一些错误操作或者实现一些业务规则；如果指定为AFTER，则表示在DML操作执行后触发，以便记录操作或做一些后期处理。
触发事件:导致触发器被触发的事件，即DML操作(插入、更新、删除)。可以是单个触发事件，也可以是多个触发事件的组合(只能用OR逻辑组合，不能用逻辑组合)。
条件谓词:当一个触发器包含多个触发事件(插入、更新、删除)的组合时，需要ORACLE提供的以下条件谓词来处理不同的事件。
1)。插入:当触发事件为插入时，值为真；否则就是假的。
2)。Updating [(column _ 1，column _ 2，…，column_x)]:当触发事件为UPDATE时，如果column_x被修改，则值为TRUE，否则为FALSE。其中column_x是可选的。
3)。删除:当触发事件为删除时，值为真，否则为假。
未发布的对象:指定在哪个表或视图上创建触发器。
触发器类型:是语句级触发器还是行级触发器
触发器条件:由WHEN子句指定逻辑表达式。在行级触发器上只能指定触发器条件，并且可以指定更新后的列列表。

触发应用程序

触发器应用复杂的安全检查。

禁止在非工作时间插入数据。

/**非工作时间（星球六 星期日， 非9点~18点的区间）禁止写入数据首先要搞清楚： 触发器的类型--语句级触发器。不管插入了多少条数据，没有必要对每一行数据都进行校验，只要不在这个时间段内，都不让插入。*/create or replace trigger addStafffCheck before insert on xgj_test declare -- local variables herebegin if to_char(sysdate, 'day') in ('星期六', '星期日') or to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then --禁止insert raise_application_error(-20001,'非工作时间禁止插入数据'); end if;end addStafffCheck;

Raise_application_error用于定制plsql使用程序中的错误消息。
该异常只能在数据库端的子程序(进程、函数、包、触发器)中使用，而不能在客户端的匿名块和子程序中使用。
语法为raise _ application _ error(error _ number，message [，[true false]])；其中error_number用于定义不正确的数字。
不正确的数字必须是介于-20000和-20999之间的负整数；Message用于指定不正确的消息，消息长度不能超过2048字节；

触发器2数据确认

工资不能上下浮动。

分析:首先确认下一个触发器的类型，这是一个行级触发器，因为每一条数据都需要确认。对于每行都是必需的。

/**涨后的薪水不能低于涨前的薪水1 :old 和 :new 代表同一条记录2 :old 代表操作该行之前，这一行的值 :new 代表操作该行之后，这一行的值*/create or replace trigger checkSalary before update on xgj_test for each rowdeclare -- local variables here 没有变量声明的话，declare可以省略begin --- if 涨后的薪水 lt; 涨前的薪水 then 如何表示呢 ？ if :new.sal lt; :old.sal then raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水：'||:old.sal); end if;end checkSalary;

使用触发器的三数据库审计

创建基于值的触发器

create table xgj_record(info varchar2(256)) ;create or replace trigger addrecord after update on xgj_test for each rowdeclare -- local variables herebegin if :new.sal gt; 6000 then insert into xgj_record values(:new.sal ||'-'|| :new.username ||'-'|| :new.job); end if;end addrecord;

触发器4数据的备份和同步

员工工资增加时，会自动备份到备份表中。

create table xgj_test_bak as select *　from xgj_test ;create or replace trigger databack after update on xgj_test for each row begin update xgj_test_bak set sal = :new.sal where username = :new.username ;end databack;

oracle中还有一种异步快照备份。对于触发器，它是同步的。

其他的

建立一个触发器，当employee表的emp表中的一条记录被删除时，将被删除的记录写入employee表删除日志表。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定触发时机为删除操作前触发 ON scott.emp FOR EACH ROW --说明创建的是行级触发器BEGIN --将修改前数据插入到日志记录表 del_emp ,以供监督使用。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );END;DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp;

限制对部门表修改(包括插入、删除、更新更新)的时间范围，即不允许在非工作时间修改部门表。

CREATE OR REPLACE TRIGGER tr_dept_timeBEFORE INSERT OR DELETE OR UPDATEON departmentsBEGIN IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表'); END IF;END;

将行触发器操作限制在部门编号为80的记录上。

CREATE OR REPLACE TRIGGER tr_emp_sal_commBEFORE UPDATE OF salary, commission_pct OR DELETEON HR.employeesFOR EACH ROWWHEN (old.department_id = 80)BEGIN CASE WHEN UPDATING ('salary') THEN IF :NEW.salary lt; :old.salary THEN RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降'); END IF; WHEN UPDATING ('commission_pct') THEN IF :NEW.commission_pct lt; :old.commission_pct THEN RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降'); END IF; WHEN DELETING THEN RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录'); END CASE;END;/*实例：UPDATE employees SET salary = 8000 WHERE employee_id = 177;DELETE FROM employees WHERE employee_id in (177,170);*/

带行触发器的级联更新。修改主表地区的region_id后，子表国家表中国家的region_id自动级联更新。

CREATE OR REPLACE TRIGGER tr_reg_couAFTER update OF region_idON regionsFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id ||'、新的region_id值是'||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id;END;

调用触发器中的过程。

CREATE OR REPLACE PROCEDURE add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type )ISBEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);END add_job_history;--创建触发器调用存储过程...CREATE OR REPLACE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROWBEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id);END;

这就是本文的全部内容。希望对大家的学习有帮助