PL/SQL DML触发器(语句级触发器、行级触发器、使用OLD和NEW谓词、使用WHEN子句、使用条件谓词、控制触发顺序FOLLOWS、DML触发器限制（重要）、DML触发器使用自治事务)

把当下的事做好

已于 2022-11-10 12:56:43 修改

阅读量1.2k

点赞数

分类专栏：笔记文章标签： oracle

于 2022-10-01 20:46:57 首次发布

本文链接：https://blog.csdn.net/weixin_43809550/article/details/127136666

版权

笔记专栏收录该内容

20 篇文章 2 订阅

订阅专栏

文章目录

DML触发器

DML触发器

Oracle可以在执行DML语句时运行触发，同时可以指定在 DML操作前或操作后进行触发，指定对每个行或语句操作上进行触发。

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}    --触发时间
{INSERT | DELETE | UPDATE [OF column [, column ...]}    --触发事件
ON [schema.] table_name   
[FOR EACH ROW ]      -- 行级触发器。默认：语句级触发器
[FOLLOWS 其它触发器名]              -- 多个触发器执行的 前后顺序
[WHEN 触发条件]
[DECLARE]： 
	[PRAGMA AUTONOMOUS_TRANSACTION;]      --自治事务声明
BEGIN
END;

语句级触发器举例

如果在创建触发器时，不指定FOR EACH ROW子句，那么创建的触发器就是语句触发器。语句触发器在建立DML操作审计，或者是DML的权限控制时非常有用，这样可以避免未经授权的DML语句操作。

示例一、只有在每个月的10日才允许办理，新员工入职与离职，其他时间不允许增加和删除员工数据

--建立表
CREATE TABLE myemp AS SELECT * FROM emp;

--创建触发器
CREATE OR REPLACE TRIGGER changemyemp_trigger
BEFORE INSERT OR DELETE ON myemp  
DECLARE
  v_curdate          VARCHAR2(20);
BEGIN
  SELECT to_char(SYSDATE, 'dd') INTO v_curdate FROM dual;
  IF trim(v_curdate) <> '10' THEN
    Raise_application_error(-20003, '在每个月的10号才允许办理入职和离职手续');
  END IF;
END changemyemp_trigger;

--向表中增加或者删除数据，如果日期不对会提示：ORA-20003: 在每个月的10号才允许办理入职和离职手续
DECLARE
BEGIN
 -- INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8888,'Test','CLERK',7369,SYSDATE,8000,NULL,10);
  DELETE FROM myemp WHERE empno = 7369;
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
END;

示例二、周末及每天下班时间（每天9：00以前，18：00以后）不允许更新myemp表

--创建触发器
create or replace trigger changemyemp_trigger
before insert or delete on myemp  
declare
	v_curhour    varchar2(20);
	v_week       varchar2(20);
begin
	select to_char(SYSDATE, 'day'), to_char(SYSDATE, 'hh24') into v_week, v_curhour from dual;
	if trim(v_week) in ('星期六', '星期日')  then
		raise_application_error(-20003, '周末不允许更新myemp表');
	elsif trim(v_curhour) < '9' or trim(v_curhour) > '18' then
		Raise_application_error(-20004, '在下班时间不允许更新myemp表');
	end if;
end changemyemp_trigger;

--向表中增加或者删除数据
declare
begin
 -- insert into myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
 -- VALUES(8888, 'Test', 'CLERK', 7369, SYSDATE, 8000, NULL, 10);
	 delete from myemp where empno = 7369;
exception
     when others then
         dbms_output.put_line(SQLERRM);
END;

示例三、每一个员工都根据基本工资收入缴税，2000以下3%，2000~5000为8%，5000以上10%，要求建立一张新的表来存放 “员工编号，姓名，工资佣金，上缴的税”，并且每次在修改员工表中的SAL和COMM字段后自动更新记录

--创建myemp_tax表
CREATE TABLE myemp_tax(
         empno  NUMBER(4),
         ename  VARCHAR2(10),
         sal    NUMBER(7,2),
         comm   NUMBER(7,2),
         tax    NUMBER(7,2),
         CONSTRAINT pk_myempno  PRIMARY KEY(empno),
         CONSTRAINT fk_myempno  FOREIGN KEY(empno) REFERENCES myemp(empno) ON DELETE CASCADE
);

--创建触发器
CREATE OR REPLACE TRIGGER myemp_out
AFTER INSERT OR UPDATE OR DELETE ON myemp  
DECLARE
	PRAGMA AUTONOMOUS_TRANSACTION;                     --触发器自主事务
	CURSOR cur_myemp IS SELECT * FROM myemp;        --定义游标找到每行的记录
	v_sal             myemp.sal%TYPE;                --定义变量计算收入
	v_myemptax        myemp_tax.tax%TYPE;            --税收
	v_myemp           myemp%ROWTYPE;
BEGIN
	DELETE FROM myemp_tax;             --清空myemp_tax表;
	FOR v_myemp IN cur_myemp LOOP
		v_sal := v_myemp.sal + nvl(v_myemp.comm, 0);          --计算总工资
		IF v_sal < 2000 THEN
			v_myemptax := v_sal * 0.03;                         --上缴税3%
		ELSIF v_sal BETWEEN 2000 AND 5000 THEN
			v_myemptax := v_sal * 0.08;                         --上缴税8%
		ELSIF v_sal > 5000 THEN
			v_myemptax := v_sal * 0.1;                         --上缴税10%
		END IF;
		INSERT INTO myemp_tax(empno,ename,sal,comm,tax)
		VALUES(v_myemp.empno,v_myemp.ename,v_myemp.sal,v_myemp.comm,v_myemptax);
	END LOOP;
	COMMIT;
END myemp_out;

--向myemp表中增加一条的记录，然后查询myemp_tax表
INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8898,'Test','CLERK',7369,SYSDATE,800,100,10);
SELECT * FROM myemp_tax;

行级触发器举例

每当一条记录出现更新操作时进行触发操作定义时要定义FOR EACH ROW

使用OLD和NEW谓词

当使用行级触发器(for each row)时，可以使用OLD和NEW谓词来获取语句执行前后的行记录。

触发语句	:old.字段	:new.字段
INSERT	未定义，字段内容为NULL	INSERT操作结束后，为增加数据值
UPDATE	更新数据前的原始值	UPDATE操作之后，更新数据后的新值
DELETE	删除前的原始值	未定义，字段内容均为NULL

NEW和OLD也包含了ROWID伪列，这个伪列在OLD和NEW中具有相同的值。
不能更改OLD记录的值，但是可以更改NEW记录的值。不过在AFTER行级触发器中也不能改变NEW，因为此时DML语句已经执行。通常来说，NEW记录仅仅在BEFORE行级触发器中被更改，OLD记录永远不能被修改，只能对其读取。
在NEW和OLD结构中不能进行记录级别的操作 ，比如直接为记录赋值时非法的，:NEW := NULL; 这样的语句是错误的，只能对谓词的每个字段进行操作。
在触发器内部，不能将NEW或OLD结构作为一个记录参数传递给过程或函数，只能传递单个的字段。
在匿名块或触发器的内部使用NEW和OLD谓词时，必须在前面加上冒号，如:NEW.empno或:OLD.empno这种格式，在其他部分使用时是不需要冒号的。

示例一、增加员工信息时，其职位必须在已经职位内选择，并且工资不能超过5000，入职日期为当前日期

--创建触发器
CREATE OR REPLACE TRIGGER myempinsert
BEFORE INSERT ON myemp  
FOR EACH ROW   
DECLARE
	v_date    DATE;
	v_jobcount    NUMBER;
BEGIN
	SELECT COUNT(empno) INTO v_jobcount FROM myemp WHERE :new.job IN(SELECT DISTINCT job FROM myemp);
	IF v_jobcount = 0 THEN            --没有找到此职位
		raise_application_error(-20009, '职位错误');
	END IF;
	IF :new.Sal > 5000 THEN
		raise_application_error(-20003, '工资不能超过5000');
	END IF;
	:new.Hiredate := SYSDATE;
END;

--调用执行
DECLARE
BEGIN
	--添加错误信息
	INSERT INTO myemp(empno, ename, job, mgr, sal, deptno)
	VALUES(9999, 'Bdqn', 'MNAGER', 7788, 8000, 10);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;

示例二、myemp工资涨幅不能超过10%

--创建触发器
CREATE OR REPLACE TRIGGER myempaddsal_trigger
BEFORE UPDATE ON myemp
FOR EACH ROW
DECLARE
BEGIN
	IF ABS((:new.sal - :old.sal) / :old.sal) > 0.1 THEN
	   raise_application_error(-20005, '工资最大涨幅不能超过10%');
	END IF;
END MYEMPADDSAL_TRIGGER;


--调用执行
DECLARE
BEGIN
	--添加错误信息
	--INSERT INTO myemp(empno, ename, job, mgr, sal, deptno)
	--VALUES(9999, 'Bdqn', 'MNAGER', 7788, 8000, 10);
	UPDATE myemp SET sal = 3000 WHERE empno = 7369;
EXCEPTION
    WHEN OTHERS THEN
         dbms_output.put_line(SQLERRM);
END;

示例三、使用序列

--创建表
create table mt(
	ID         NUMBER,
	NAME       VARCHAR2(30),
	address    varchar2(50),
	CONSTRAINT pd_mtid PRIMARY KEY(ID)
);

--创建序列
create sequence mt_se;

--创建触发器
create or replace trigger mt_trigger
before insert on mt  
for each row
declare
begin
  	select mt_se.NEXTVAL into :new.id from dual;
end mt_trigger;

--调用执行
DECLARE
BEGIN
	--插入数据，发现ID自动增加
	INSERT INTO mt(name,address)VALUES('张三', '深圳');
	INSERT INTO mt(name,address)VALUES('李四', '深圳');
END;
```示例一、增加员工信息时，其职位必须在已经职位内选择，并且工资不能超过5000，入职日期为当前日期

```sql
--创建触发器
CREATE OR REPLACE TRIGGER myempinsert
BEFORE INSERT ON myemp  
FOR EACH ROW   
DECLARE
	v_date    DATE;
	v_jobcount    NUMBER;
BEGIN
	SELECT COUNT(empno) INTO v_jobcount FROM myemp WHERE :new.job IN(SELECT DISTINCT job FROM myemp);
	IF v_jobcount = 0 THEN            --没有找到此职位
		raise_application_error(-20009, '职位错误');
	END IF;
	IF :new.Sal > 5000 THEN
		raise_application_error(-20003, '工资不能超过5000');
	END IF;
	:new.Hiredate := SYSDATE;
END;

--调用执行
DECLARE
BEGIN
	--添加错误信息
	INSERT INTO myemp(empno, ename, job, mgr, sal, deptno)
	VALUES(9999, 'Bdqn', 'MNAGER', 7788, 8000, 10);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;

使用WHEN子句

在这里插入图片描述

在WHEN子句中可以使用不带冒号的NEW和OLD，如WHEN (NEW.sal>OLD.sal)。

要求工资只能涨不能降

--创建触发器
create or replace trigger myemptestadd1
  before UPDATE on myemp  
  for each ROW
   when(new.Sal < old.Sal)
begin
  raise_application_error(-20003,:old.EMPNO||'的工资只能涨不能降!');
end myemptestadd1;

--执行错误的更新
BEGIN
 UPDATE myemp SET sal=2000 WHERE empno=7788;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

--结果
ORA-20003: 7788的工资只能涨不能降!
ORA-06512: 在 "TESTS.MYEMPTESTADD1", line 4
ORA-04088: 触发器 'TESTS.MYEMPTESTADD1' 执行过程中出错

一个很不错的问题：https://cloud.tencent.com/developer/ask/sof/471322

使用条件谓词

条件谓词主要用来确定触发器的DML语句的类型，条件谓词有INSERTING、UPDATING、DELETING，都返回一个布尔值True或False。

//比如
IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;
IF DELETING THEN ... END IF;

UPDATING有一个重载的版本接收特定的列名，以便根据更新的列来进行进一步的处理，如:

CREATE OR REPLACE TRIGGER t_comm_sal
   BEFORE UPDATE ON emp     --触发器作用的表对象以及触发的条件和触发的动作
   FOR EACH ROW             --行级别的触发器
BEGIN
   CASE 
   WHEN UPDATING('comm') THEN          --如果是对comm列进行更新     
      IF :NEW.comm<:OLD.comm THEN      --要求新的comm值要大于旧的comm值
         RAISE_APPLICATION_ERROR(-20001,'新的comm值不能小于旧的comm值');
      END IF;
   WHEN UPDATING('sal') THEN           --如果是对sal列进行更新
      IF :NEW.sal<:OLD.sal THEN        --要求新的sal值要大于旧的sal值
         RAISE_APPLICATION_ERROR(-20001,'新的sal值不能小于旧的sal值'); 
      END IF;
   END CASE;        
END;

控制触发顺序FOLLOWS

更详细得见：https://blog.csdn.net/yoursly/article/details/79360648

Oracle11g开始提供了一个触发器顺序控制的功能，对于同时触发的多个触发器，可以用FOLLOWS语句来控制先后执行顺序。

但是FOLLOWS语法有一定的限制：只能用在同类型的多个触发器上（before、after要一样；update、insert、delete要一样；行级、语句级要一样）

--创建第一个触发器
CREATE OR REPLACE TRIGGER one_trigger
   BEFORE INSERT
   ON trigger_data
   FOR EACH ROW
BEGIN
   :NEW.trigger_id := :NEW.trigger_id + 1;
   DBMS_OUTPUT.put_line('触发了one_trigger');
END;

--创建与第1个触发器具有相同类型相同触发时机的触发器
CREATE OR REPLACE TRIGGER two_trigger
   BEFORE INSERT
   ON trigger_data
   FOR EACH ROW
   FOLLOWS one_trigger          --让该触发器在one_trigger后面触发
BEGIN
   DBMS_OUTPUT.put_line('触发了two_trigger');
   IF :NEW.trigger_id > 1
   THEN
      :NEW.trigger_id := :NEW.trigger_id + 2;
   END IF;
END;

实际上，使用了FOLLOWS语句后，在两个触发器之间创建了依赖，使得two_trigger依赖于one_trigger，可以通过user_dependencies查看依赖关系。

不过一般不推荐使用使用FOLLOWS来控制顺序，因为使用一个触发器就已经不是一个很好的解决方案了，同时使用两个触发器就更不推荐了。

DML触发器限制（重要）

在编写DML触发器 代码时，要注意不能对DML触发器所应用的基表中读取或修改数据，尽管这样做在建立触发器时不会报错，但是在执行相应的触发器时会显示错误消息。因此，要注意：

通常，行级别的DML触发器不能读或写触发器所作用的基表，这个限制仅应用在行级别DML触发器上，语句级的DML触发器可以自由读写触发器基表。
如果在DML触发器中使用自治事务，并在触发体中提交事务，则可以查询基表的内容，但是不能对基表进行任何的修改操作。

DML触发器使用自治事务

默认情况下，DML触发器与触发其执行的DML语句在一个事务范围内，因此：

如果在触发器中抛出了一个异常，将导致整个事务回滚。
如果在触发体中使用了DML操作，比如向日志表中插入日志记录，那么这些DML操作也属于主事务的一部分，因此触发体中的任何意外操作也会导致整个事务回滚。
在触发体中不能使用COMMIT或ROLLBACK语句，因为这回影响到主事务的执行。

如果在编写触发器时，需要独立主事务进行处理，比如不管DML语句是否成功执行，都需要将对日志的操作保存到数据库中，此时可以使用自治事务，对主事务不会有影响。
与在过程和函数中使用自治事务一样，使用PRAGMA AUTONOMOUS_TRANSACTION编译提示，如：

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;

把当下的事做好

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL DML触发器(语句级触发器、行级触发器、使用OLD和NEW谓词、使用WHEN子句、使用条件谓词、控制触发顺序FOLLOWS、DML触发器限制（重要）、DML触发器使用自治事务)

实际上，使用了FOLLOWS语句后，在两个触发器之间创建了依赖，使得two_trigger依赖于one_trigger，可以通过user_dependencies查看依赖关系。在编写触发器代码时，要注意不能对触发器所应用的基表中读取或修改数据，尽管这样做在建立触发器时不会报错，但是在执行相应的触发器时会显示错误消息。如果在编写触发器时，需要独立主事务进行处理，比如不管DML语句是否成功执行，都需要将对日志的操作保存到数据库中，此时可以使用自治事务，对主事务不会有影响。要求工资只能涨不能降。
复制链接

扫一扫