PL/SQL触发器

已于 2022-07-11 08:05:11 修改
阅读量1.1k 收藏 3
点赞数
分类专栏： 数据库 文章标签： sql 数据库
于 2022-07-07 23:07:51 首次发布
tubumoxi@lyg
本文链接：https://blog.csdn.net/weixin_46646680/article/details/125665270
版权

文章目录

触发器的概述

提示：触发器简来说就隐式被执行的过程

分类：

  1. dml触发器
  2. 系统触发器
  3. ddl触发器

一、触发器

1.语法：

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
[FOR EACH ROW]
[WHEN CONDITION]
BEGIN
TRIGGER_BODY;
END;
语法说明
  • or replace 覆盖
  • before 在触发之前执行
  • after在触发事件之后执行
  • insert/delete/update 在插入、删除、更新操作时触发
  • or 可以多个操作同时定义触发器
  • on 对哪一个表或视图进行监视
  • for each row 带上也就是记录触发器（行级触发器）比如删除一条执行触发器一次
  • when condition 条件表达式
    代码实现：在员工表插入数据前打印helloworld
--在员工表插入记录前 打印hello world

CREATE TRIGGER  MY_TRIGGER1
BEFORE
INSERT --在插入数据时执行触发器
ON SCOTT.EMP --对scott下的emp表进行监视
FOR EACH ROW --行级触发器
  BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLOWROLD');
  END;

--测试触发器
INSERT INTO EMP(EMPNO,ENAME) VALUES(2569,'HAHA');
COMMIT;

代码实现二：为了禁止工作人员在周四周五改变员工信息，（before改变前执行触发器）

-- 为了禁止工作人员在周四周五改变员工信息，（before改变前执行触发器）
CREATE TRIGGER MY_TRIGGER2
BEFORE
INSERT OR UPDATE OR DELETE
ON SCOTT.EMP
FOR EACH ROW --行级触发器
  BEGIN
    IF TO_CHAR(SYSDATE,'DAY') IN ('星期四','星期五') THEN
      --抛出异常 ，提醒不能更新员工信息
      RAISE_APPLICATION_ERROR(-1000,'对不起，休息日不能更改员工信息');
    END IF;
  END;
  
SELECT to_char(SYSDATE,'DAY') FROM dual;
DELETE FROM EMP WHERE EMPNO=2569;
SELECT * FROM EMP;

代码实现三：对所有员工薪资加1000，并打印旧的薪资和新的薪资

CREATE TRIGGER MY_TRIGGER3
AFTER
UPDATE 
ON SCOTT.EMP
FOR EACH ROW --行级触发器
  BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLOWORLD');
  END;

UPDATE EMP SET SAL=SAL+1000;
-- 对所有员工薪资加1000，并打印旧的薪资和新的薪资
CREATE OR REPLACE TRIGGER MY_TRIGGER3
AFTER
UPDATE ON SCOTT.EMP
FOR EACH ROW --行级触发器
  BEGIN
  --返回旧的薪资:old.sal,新new.sal    DBMS_OUTPUT.PUT_LINE('OLD SAL:'||:OLD.SAL||',NEW SAL:'||:NEW.SAL);
  END;

UPDATE EMP SET SAL=SAL+1000;
COMMIT;

在这里插入图片描述

代码实现四：创建emp备份表(my_emp)，编写触发器，在对my_emp数据进行删除的时，在my_emp_bak备份表插入my_emp删除的数据

-- 创建emp备份表(my_emp)，编写触发器，在对my_emp数据进行删除的时，在my_emp_bak备份表插入my_emp删除的数据
--创建my_emp表
CREATE TABLE MY_EMP
AS
SELECT  EMPNO,ENAME,SAL 
FROM EMP;
--查询my_emp表
SELECT * FROM MY_EMP;
--创建my_emp_bak表结构
CREATE TABLE MY_EMP_BAK
AS
SELECT * FROM MY_EMP
WHERE 1=0;
-- 查寻my_emp_bak表
SELECT * FROM MY_EMP_BAK;

SELECT * FROM EMP_1;
DELETE FROM EMP;
COMMIT;

SELECT * FROM EMP;
CREATE TABLE EMP AS SELECT * FROM EMP_1;

CREATE OR REPLACE TRIGGER MY_TRIGGER4
BEFORE
DELETE
ON SCOTT.MY_EMP
FOR EACH ROW --行级触发器
  BEGIN
    INSERT INTO MY_EMP_BAK VALUES(:OLD.EMPNO,:OLD.ENAME,:OLD.SAL);
    --DML 操作无需commit
  END;
--测试触发器
DELETE FROM MY_EMP;
COMMIT;
SELECT * FROM MY_EMP;
SELECT * FROM MY_EMP_BAK;

2、对触发器的禁用启用删除操作

--禁用触发器
ALTER TRIGGER MY_TRIGGER4 DISABLE;
--启用触发器
ALTER TRIGGER MY_TRIGGER4 ENABLE;
--删除触发器
DROP TRIGGER MY_TRIGGER4;

3、练习

代码实现一：创建触发器，它将映射emp表中每个部门的总人数和总工资

--创建触发器，它将映射emp表中每个部门的总人数和总工资

--首先创建映射表（表的备份将查询结果作为表内容）
CREATE TABLE DEPT_SAL 
AS
SELECT DEPTNO,COUNT(EMPNO) TOTAL_EMP,SUM(SAL) TOTAL_SAL
FROM EMP
GROUP BY DEPTNO;
--查看映射表
SELECT * FROM DEPT_SAL;

-- 创建触发器
CREATE OR REPLACE TRIGGER MY_TRIGGER5
AFTER
INSERT OR UPDATE OR DELETE
ON EMP
DECLARE
  CURSOR CUR_EMP 
  IS
  SELECT DEPTNO,COUNT(EMPNO) TOTAL_EMP,SUM(SAL) TOTAL_SAL 
  FROM EMP 
  GROUP BY DEPTNO;
BEGIN
  DELETE DEPT_SAL;
  FOR V_EMP IN CUR_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
    INSERT INTO DEPT_SAL VALUES(V_EMP.DEPTNO,V_EMP.TOTAL_EMP,V_EMP.TOTAL_SAL);
  END LOOP;
END;
-- 对emp表进行DML操作
SELECT * FROM EMP;
INSERT INTO emp(empno,deptno,sal) VALUES(123,10,10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;

代码实现二：创建触发器，用来记录表删除数据

--创建触发器，用来记录表的删除数据

-- 创建表
CREATE TABLE EMPLOYEE(
  ID VARCHAR2(4) NOT NULL,
  NAME VARCHAR2(15) NOT NULL,
  AGE NUMBER(2) NOT NULL,
  SEX CHAR NOT NULL
);
--插入数据
INSERT INTO EMPLOYEE 
VALUES('E101','ZHAO',23,'M');
INSERT INTO EMPLOYEE 
VALUES('E102','JIAN',21,'F');
COMMIT;
--创建记录表
CREATE TABLE OLD_EMP AS SELECT * FROM EMPLOYEE WHERE 1=0;
--查看表数据
SELECT * FROM EMPLOYEE;
SELECT * FROM OLD_EMP;
-- 创建触发器
CREATE OR REPLACE TRIGGER MY_TIRGGER6
AFTER 
DELETE
ON EMPLOYEE
FOR EACH ROW
BEGIN 
  INSERT INTO OLD_EMP 
  VALUES(:OLD.ID,:OLD.NAME,:OLD.AGE,:OLD.SEX);
END;
--测试代码
DELETE FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
SELECT * FROM OLD_EMP;

代码实现三：创建触发器，利用视图插入数据

--创建触发器利用视图插入数据

--创建表
CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2));
CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30));

--插入数据
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
COMMIT;
--创建视图连接两张表
CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid;

--创建触发器
CREATE OR REPLACE TRIGGER MY_TRIGGER7
INSTEAD OF 
INSERT 
ON TAB_VIEW
BEGIN
  INSERT INTO TAB1(TID,TNAME) VALUES(:NEW.TID,:NEW.TNAME);
  INSERT INTO TAB2(TTEL,TADR) VALUES(:NEW.TTEL,:NEW.TADR);
  
END;

--测试数据
INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
--查询
SELECT * FROM tab_view;
SELECT * FROM tab1;
SELECT * FROM tab2;

代码实现：创建触发器，比较emp表更新的薪资

--创建触发器 比较emp中更新的工资

CREATE OR REPLACE TRIGGER MY_TRIGGER8
BEFORE
UPDATE
ON EMP
FOR EACH ROW
BEGIN 
  IF :OLD.SAL>:NEW.SAL THEN
    DBMS_OUTPUT.PUT_LINE('工资减少了');
  ELSIF :OLD.SAL<:NEW.SAL THEN
    DBMS_OUTPUT.PUT_LINE('工资增加了');
  ELSE
    DBMS_OUTPUT.PUT_LINE('工资没变');
  END IF;
  DBMS_OUTPUT.PUT_LINE('OLDSAL'||:OLD.SAL);
  DBMS_OUTPUT.PUT_LINE('NEW.SAL'||:NEW.SAL);
END;

--测试

UPDATE EMP SET SAL=8000 WHERE EMPNO='7369';
COMMIT;

代码实现：创建触发器，将create、drop 存储在log_info中

--创建触发器 ，将操作create 、drop存储在log_info 表中

CREATE TABLE log_info(
  manager_user VARCHAR2(15),
  manager_date VARCHAR2(15),
  manager_type VARCHAR2(15),
  obj_name   VARCHAR2(15),
  obj_type   VARCHAR2(15)
);

-- 创建触发器

CREATE OR REPLACE TRIGGER MY_TRIGGER8
AFTER
CREATE OR DROP
ON SCHEMA
BEGIN
  INSERT INTO LOG_INFO
  VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER,SYS.DICTIONARY_OBJ_TYPE);
END;

--测试
CREATE TABLE A(ID NUMBER);
CREATE TYPE AA AS OBJECT(ID NUMBER);
DROP TABLE A;
DROP TYPE AA;

--看效果
SELECT * FROM LOG_INFO;
--相关数据字典
SELECT * FROM USER_TRIGGERS;

注意

  • Oracle数据库触发器不能调用commit，rollback 等dcl语句
  • 在触发器中不能运行ddl语句和commit，rollback语句
  • ddl语句：DDL语句⽤语定义和管理数据库中的对象，如Create,Alter，Drop，truncate等；DDL操作是隐性提交的！
    触发器使用ddl语句

-在可以在触发器中加⼊：pragma autonomous_transaction;(在DECLARE后⾯) 表⽰是⾃由事务处理。

CREATE OR REPLACE TRIGGER T_create BEFORE insert ON T_Tax_INS_BD
for each row
DECLARE
pragma autonomous_transaction; 
NRDSId varchar(500):=''; 
begin

⼀： 在触发器中使⽤DDL语句。 如 drop table t1 ，触发的时候会报错，
ORA-04092: cannot COMMIT in a trigger，因为DDL语句隐含commit。
⼆： 触发器的主体中使⽤了commit或rollback等DCL语句。ORA-04092:
cannot COMMIT in a trigger ， 因为DML（delete/update/insert）触发器中
不能使⽤DDL（CREATE，DROP，ALTER）语句，也不能使⽤事务控制语句(DCL)
（ROLLBACK, COMMIT,SAVEPOINT）。特别注意的是，在触发器的主体中引⽤的
函数（function）/过程（procedure）中也不能有事物控制语句。
三： 注意： 系统级触发器（System Triggers）中可以使⽤DDL语句。
四： 处理⽅法： 去掉事务控制语句；如果procedure中必须有commit，那么可以
将commit 拿掉，由外部控制 。

总结

没有自治事务，在trigger就不能写commit

了解自治事务：转载

贫僧会写代码
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
PLSQL 触发器
03-14
触发器学习 PLSQL 语言学习 触发器的使用 一个ppt文档
ORACLE PL/SQL 触发器编程篇介绍
09-11
ORACLE PL/SQL 触发器能够完成由数据库的完整性约束难以完成的复杂业务规则的约束；监视数据库的各种操作以及实现审计功能
精通Oracle PL/SQL 图灵
09-14
对于oracle技术人员而言，怎么强调掌握pl/sql的重要性都不过分。但是，真正精通pl/sql绝非易事。事实上，在现有的oracle应用程序中充斥着太多质量不佳的pl/sql代码，它们要么没有充分利用oracle特有的功能，要么是在误用或者滥用。这种现象导致了许多对pl/sql本身能力不公正的批评。. 　　 本书由全球oracle技术专家组织oaktable network的多位大师联手编写，是为pl/sql正名的名著。与市面上许多pl/sql图书只是纸上谈兵、局限于语法和功能本身不同，本书从实战出发，讲述如何正确、充分地运用语言特性，除了pl/sql的基本功能之外，也包括数据的高效处理、安全、触发器、高效的调试技术等。作者在讲解每一个特性 时都辅以经过实战检验的示例，更与读者分享了大量多年积累的独家经验。有本书在手，你将学会如何充分发挥pl/sql的巨大潜能，编写出可靠、高效且易于 维护的一流程序。...
PL/SQL 程序设计
01-28
PL/SQL 程序设计 本章主要重点：  PL/SQL概述  PL/SQL块结构  PL/SQL流程  运算符和表达式  游标  异常处理  数据库存储过程和函数  包  触发器
PL/SQL学习笔记
11-01
PL/SQL从入门到精通学习帮助手册,PL/SQL简介,PL/SQL操作,存储过程,触发器等
SQL触发器在存放在哪个位置
热门推荐
wozengcong的专栏
05-29 1万+
可以使用下面语句查看某个库当前的触发器 USE [pratice] GO Select * FROM sys.objects WHERE [type] = 'tr' Select * FROM sys.[sysobjects] WHERE [xtype] = 'tr' 不过只能看到DML触发器 如果是DDL或者登录触发器是看不到的 SQLSERVER中的三
PLSQL——触发器
m0_72036727的博客
07-20 4198
触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用。
pl/sql篇之触发器
weixin_73368873的博客
02-24 1598
触发器是数据库独立编译，存储的对象，是数据库重要的技术。和函数不同，触发器的执行是主动的，它监控着数据库，满足其触发条件自动触发，便于我们对数据库的管理。所以给出了替代触发器。由于触发器的隐蔽性，数据更新失败，难以追溯，逻辑的修改需要修改源码，由于触发器的源码可读性较差，导致这一过程，变得更加麻烦。ORACLE可以在DML(数据更新语句）进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。触发器的触发条件在事件发生前或发生后，主要用于DML语句下，触发的，一共有12种类型。
7. PLSQL--程序包和触发器
weixin_46760148的博客
02-27 482
PLSQL--程序包和触发器1.11 程序包（package）1.11.1包的组成1.11.2.包的作用1.12触发器1.12.1触发事件分类 1.11 程序包（package） 1.11.1包的组成 包由两部分组成，分别为包的声明部分和包的实现部分 1.包的声明部分 2.包的实现部分 1.11.2.包的作用 使用包将程序分组归类 使用私有方法隐藏逻辑----》隐藏函数，在程序包中存在，但是只能由包的内部成员才能调用。 被调用函数，一定要写在调用函数的前面 在上面的例子中，我们将与圆相关的计算
Oracle & PL/SQL 存储过程
Jancy2265的博客
09-08 831
Oracle PL/SQL 存储过程 Oracle 简介： 是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。 oracle安装 Oracle一般安装于公司服务器中，更多应该关注于客户端连接数据方法。 oracle连接 下载oracle客户端连接程序 https://www.ora
PL/SQL课件
07-12
PL/SQL简介（第1~2章） PL/SQL中的SQL语句（第3章） 控制结构（第4~7章） 异常处理（第8~10章） 游标（第11~12章） 触发器（第13~14章） 复合数据类型（第15~16章） 动态SQL（第17章） 批量SQL（第18章） 子程序和包（第19~22章） Oracle中对象类型（第23章） Oracle提供的包（第24章）
oracle pl/sql 存储过程和函数与触发器
04-20
oracle pl/sql 存储过程和函数与触发器
PL/SQL系统触发器(DDL触发器事件、数据库系统触发器事件、触发器属性列表）
weixin_43809550的博客
09-30 922
DDL event包括CREATE、DROP、ALTER、GRANT、RENAME、REVOKE、TRUNCATE、ANALYZE、ASSOCIATE STATISTICS、AUDIT、COMMENT等。，对每一个触发的事件，Oracle会打开一个匿名事务，触发触发器，提交任何独立的事务，这些事件有SERVERERROR、LOGON、LOGOFF、STARTUP、SHUTDOWN、SUSPEND。在创建数据库级的触发器时，必须具有ADMINISTER DATABASE TRIGGER的系统权限。
PL/SQL触发器的概述和用途
jakelihua
09-25 303
在数据库中，触发器是一种特殊的存储过程，它在特定的数据库事件发生时自动执行。触发器可以用于在数据插入、更新或删除之前或之后执行自定义的逻辑。它们提供了一种在数据库层面上实现业务规则和数据完整性的方法。
PL/SQL教程之八：触发器
一切都是浮云
02-19 814
触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。 8.1触发器类型 　　触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（fi...
PLSQL触发器
weixin_45083214的博客
08-11 2467
在DML触发器中，由于被INSERT、UPDATE、DELETE 所引起，分别对应三种布尔类型的数据：INSERTING、UPDATING、DELETING。触发器作为数据库对象之一，并不像函数或是存储过程那样被调用，而是由特定的语句所引起/触发所执行的.顾名思义，当满足触发器执行的条件后，不真正执行用户选中的DML语句转而执行触发器中的逻辑代码。--由于触发器没有参数，所以不需要接受，同时需要定义对应触发器的属性特点。--创建一张表，字段（id-主键，name-普通字段），--触发的对象是一整张表。...
PLSQL触发器（表级、行级、替换、模式、数据库级）
tcz_666的博客
09-25 512
触发器是一种在事件发生时自动执行的PLSQL块，不接收参数，不能被调用。触发器的类型有：DML触发器（表级触发器、行级触发器、替换触发器）、模式（DDL)触发器、数据库级触发器。--给dept添加行级触发器，当修改dept的部门编号时，员工表的部门编号也发生变化,当删除部门时，员工表中改部门的员工被删除。--创建一个显示员工编号、员工姓名、所在部门号及部门名的视图,利用触发器实现向该视图中添加数据的操作。--给dept表创建一个触发器，当dept表中的数据被修改、删除、插入时打印该操作。
SQL进阶（六）：通关题：制作一个活动日历
最新发布
qq_33489955的博客
05-15 613
就是求当月的最后一天是周几，周六算1天，周日算两天SELECT(CASEELSE 0FROMGROUP BYyear_monthSELECTFROM[(5,)]a4 = '5' # 在 '' 中填入你的结果，如 a4 = '5' 代表有个 5 个周末（周六，周日分开计算，例如 2个周六 和 3个周日 = 5个周末）
pl/sql如何删除触发器
03-29
要删除PL/SQL触发器，可以使用DROP TRIGGER语句。以下是删除触发器的语法： ``` DROP TRIGGER trigger_name; ``` 其中，trigger_name是要删除的触发器的名称。执行此语句后，触发器将被删除。请注意，删除触发器将导致触发器不再起作用，因此请谨慎操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值