【postgresql初级使用】视图上的触发器instead of,替代计划的rewrite,实现不一样的审计日志

instead of 触发器

专栏内容

  • postgresql使用入门基础
  • 手写数据库toadb
  • 并发编程

个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

文章目录

  • instead of 触发器
  • 概述
  • 机制原理
    • INSTEAD OF 触发器语法
    • INSTEAD OF 触发器执行步骤
    • 多触发器的优先级
  • 审计案例解析
    • 数据准备
      • 创建两张数据表
      • 创建视图
      • 创建执行函数
      • 创建触发器
    • 结果展示
      • 信息查看
      • 新员工入职
      • 变岗调薪
  • 总结
  • 结尾

概述


在postgresql 中,触发器与SQL标准相比,有一个扩展特性就是可以在视图上创建触发器。

在视图上的触发器类型是’INSTEAD OF`,可以在insert, update, delete事件的before或after进行触发,
本文就来详细分享一下,并通过一个实用的审计数据的案例来展示效果。

机制原理


下面先来介绍SQL语法,然后通过介绍它的执行过程,了解它的机制原理。

INSTEAD OF 触发器语法

视图触发器的创建语法如下:

CREATE TRIGGER trigger_name
INSTEAD OF [INSERT OR UPDATE OR DELETE]
ON table_name
FOR EACH ROW 
EXECUTE FUNCTION trigger_function;

在视图上的触发器,类型只有INSTEAT OF,而且它对应的触发器件有insert,update,delete,此处没有truncate事件。

另外,instead of 触发器也只能定义为行级触发器,这个原因下面会分析。

INSTEAD OF 触发器执行步骤

视图触发器执行的步骤如下

  • 当在视图上执行INSERT、UPDATE或DELETE操作时,如果为该视图定义了INSTEAD OF触发器,那么这些操作不会直接对视图或其基础表执行。
  • 相反地,INSTEAD OF触发器会代替这些操作执行。也就是说,对于需要修改的每一行,都会触发INSTEAD OF触发器。
  • 触发器的函数需要负责执行必要的修改到视图的基础表,并在适当的情况下返回修改后的行,以便在视图中显示。
  • 这允许开发者在不影响基础表结构的情况下,通过视图执行复杂的操作逻辑。

在这里插入图片描述

如果视图上没有INSTEAD OF触发器,那么对视图的操作必须被转换为对基础表的操作,是直接操作基础表。

多触发器的优先级

  • 除了INSTEAD OF触发器外,还可以在视图上定义在INSERT、UPDATE或DELETE操作之前或之后执行的触发器。
  • 但是,这些触发器只有在视图上也有INSTEAD OF触发器的情况下才会被触发。
  • 如果没有INSTEAD OF触发器,那么任何针对视图的SQL语句都必须被重写为影响基础表的语句,然后触发的是附加到基础表上的触发器。

审计案例解析


为了信息的安全,一般系统都会有审计这个功能,其中审计日志会把操作记录详细记录下来,会定期时行审计或者出问题时能够帮助回溯。

下面就来分享一个通过触发器实现的审计日志功能,大概设计如下:

  • 应用一般不会对物理表进行直接操作,而对给它们创建对应的视图,只看到部分数据;
  • 对于每个视图的操作事件,创建instead of类型的触发器;
  • 在视图触发器中,对于操作的类型,数据,以及操作者用户都记录到表中;

数据准备

创建两张数据表

  • 一张是数据信息表emp,记录员工的薪记信息;
  • 一张是审计数据表emp_audit, 记录操作类型,操作员,数据变动,以及时间。
CREATE TABLE emp (
  empname text PRIMARY KEY,
  salary integer
);
CREATE TABLE emp_audit(
  operation char(1) NOT NULL,
  userid text NOT NULL,
  empname text NOT NULL,
  salary integer,
  stamp timestamp NOT NULL
);

创建视图

CREATE VIEW emp_view AS
SELECT e.empname,
    e.salary,
    max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;

创建执行函数

  • 在delete时,先删除基础表的数据;如果删除成功,再插入审计表,操作类型为’D’,否则不操作审计表;
  • 在update时,同上,先更新基础表;如果成功,才插入审计表;
  • 在insert时,先插入基础表;然后操作审计表;
  • 每次都将操作时间更新为当前时间;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
      DELETE FROM emp WHERE empname = OLD.empname;
      IF NOT FOUND THEN 
          RETURN NULL; 
      END IF;

      OLD.last_updated = now();
      INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
      RETURN OLD;
  ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;

      IF NOT FOUND THEN 
          RETURN NULL; 
      END IF;

      NEW.last_updated = now();
      INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
      RETURN NEW;
  ELSIF (TG_OP = 'INSERT') THEN
      INSERT INTO emp VALUES(NEW.empname, NEW.salary);
      NEW.last_updated = now();
      
      INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
      RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

创建触发器

在视图emp_view上创建instead of类型的触发器,使用上面定义的函数。

CREATE TRIGGER emp_audit
  INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
  FOR EACH ROW EXECUTE FUNCTION update_emp_view();

结果展示

测试一下审计模块的效果。

信息查看

员工管理系统中,对于薪资表结构如下:

postgres=> \d emp_view
                          View "senlleng.emp_view"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 empname      | text                        |           |          |
 salary       | integer                     |           |          |
 last_updated | timestamp without time zone |           |          |
Triggers:
    emp_audit INSTEAD OF INSERT OR DELETE OR UPDATE ON emp_view FOR EACH ROW EXECUTE FUNCTION update_emp_view()

新员工入职

有新员工入职,录入新员工的薪资。

postgres=> insert into emp_view values('zhanglei', 10000);
INSERT 0 1
postgres=> insert into emp_view values('wangguo', 8000);
INSERT 0 1
postgres=> select * from emp_audit ;
 operation |  userid  | empname  | salary |           stamp
-----------+----------+----------+--------+----------------------------
 I         | senllang | zhanglei |  10000 | 2024-06-06 08:13:05.829596
 I         | senllang | wangguo  |   8000 | 2024-06-06 08:13:24.125127
(2 rows)

有两名新员工入职,可以看到是那个操作员录入的,并且当前录入的时间,信息都可以看到。

变岗调薪

当人员调岗时,对应的薪资也会发生变化;

或者人员离职时,需要删除对应的记录。

postgres=> update emp_view set salary = 7500 where empname='wangguo';
UPDATE 1
postgres=> delete from emp_view where empname='zhanglei';
DELETE 1
postgres=> select * from emp_audit ;
 operation |  userid  | empname  | salary |           stamp
-----------+----------+----------+--------+----------------------------
 I         | senllang | zhanglei |  10000 | 2024-06-06 08:13:05.829596
 I         | senllang | wangguo  |   8000 | 2024-06-06 08:13:24.125127
 U         | senllang | wangguo  |   7500 | 2024-06-06 08:14:30.737416
 D         | senllang | zhanglei |  10000 | 2024-06-06 08:14:53.089083
(4 rows)

当然审计表的权限是非常高的,只有在审计系统中才能查看,而且审计数据是不能删除的。

总结


在视图上的instead of 触发器,可以将原本的执行计划重写 替换为触发器执行,这样可以进行更为复杂的动作,这里以审计为例,演示了触发器的效果。

结尾


非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/690053.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Linux宝塔部署数据库连接问题

博主在部署项目时发现网页可以成功部署,但是登录界面一直登录不进去推测是数据库连接问题。 博主当时在IDEA中写的是用户名为root 密码123456 但是在宝塔中因为自己是跟着教程学的所以就顺手把用户名和密码都改了,于是java中的配置和数据库配置连接不上…

C++第二十五弹---从零开始模拟STL中的list(下)

✨个人主页: 熬夜学编程的小林 💗系列专栏: 【C语言详解】 【数据结构详解】【C详解】 目录 1、函数补充 2、迭代器完善 3、const迭代器 总结 1、函数补充 拷贝构造 思路: 先构造一个头结点,然后将 lt 类中的元…

深入探索:十种流行的深度神经网络及其运作原理

算法 深入探索:十种流行的深度神经网络及其运作原理一、卷积神经网络(CNN)基本原理工作方式 二、循环神经网络(RNN)基本原理工作方式 三、长短期记忆网络(LSTM)基本原理工作方式 四、门控循环单…

.net core 使用js,.net core 使用javascript,在.net core项目中怎么使用javascript

.net core 使用js,.net core 使用javascript,在.net core项目中怎么使用javascript 我项目里需要用到“文字编码”,为了保证前端和后端的编码解码不处bug, 所以,我在项目中用了这个 下面推荐之前在.net F4.0时的方法 文章一&#…

js--hasOwnProperty()讲解与使用

@TOC 前言 hasOwnProperty(propertyName)方法 是用来检测属性是否为对象的自有属性 object.hasOwnProperty(propertyName) // true/false 讲解 hasOwnProperty() 方法是 Object 的原型方法(也称实例方法),它定义在 Object.prototype 对象之上,所有 Object 的实例对象都会继…

下载中心表设计

文件表 有哪些文件需要异步生成 文件夹表 添加文件夹功能时使用 权限表 文件权限绑定 对用户来说,下载文件和配置下载管理是两个可直接交互的功能。下载文件包括: 1)添加下载任务(手动开始)。 2)开始…

安卓约束性布局学习

据说这个布局是为了解决各种布局过度前套导致代码复杂的问题的。 我想按照自己想实现的各种效果来逐步学习,那么直接拿微信主页来练手,用约束性布局实现微信首页吧。 先上图 先实现顶部搜索框加号按钮 先实现 在布局中添加一个组件,然后摆放…

Java学习54-关键字this的使用

this是什么 this的作用: 它在方法(准确的说是实例方法或非static的方法)内部使用,表示调用该方法的对象 它在构造器内部使用,表示该构造器正在初始化的对象 this可以调用的结构:成员变量、方法和构造器 什么时候使用this 实…

安徽代理记账公司的专业服务和创新理念

在当今竞争激烈的市场环境中,为了提升企业的运营效率,许多企业开始寻找专业的代理记账公司进行财务管理和记账,本文将介绍一家名为安徽代理记账公司的专业服务和创新理念。 安徽代理记账公司是一家专注于为企业提供全方位会计服务的公司&…

[ 网络通信基础 ]——网络的传输介质(双绞线,光纤,标准,线序)

🏡作者主页:点击! 🤖网络通信基础TCP/IP专栏:点击! ⏰️创作时间:2024年6月8日14点23分 🀄️文章质量:94分 前言—— 在现代通信网络中,传输介质是数据传…

江西代理记账公司的专业服务和优质品质

作为一家专业的代理记账公司,我们始终以“专业、公正、公平”为宗旨,为客户提供全方位的会计咨询服务,我们的服务内容包括但不限于以下几点: 1、代理记账服务:我们拥有丰富的经验和专业知识,能够为企业提供…

【ARM Cache 系列文章 1.2 -- Data Cache 和 Unified Cache 的详细介绍】

请阅读【ARM Cache 及 MMU/MPU 系列文章专栏导读】 及【嵌入式开发学习必备专栏】 文章目录 Data Cache and Unified Cache数据缓存 (Data Cache)统一缓存 (Unified Cache)数据缓存与统一缓存的比较小结 Data Cache and Unified Cache 在 ARM架构中,缓存&#xff08…

一次改SQLMAP的操作

前言 sqlmap这个工具,相信各位大佬们都不陌生,但sqlmap虽好,也时常会有些实际存在但无法注入的地方,这时候就需要我们改它的配置了,今天就以本人遇到的事件进行阐述。 正文 确认注入点 通过一系列测试最终确定这里…

论文高级图表绘制(Python语言,局部放大图)

本文将通过一个具体的示例,展示如何使用Python语言和Matplotlib库来绘制高级图表,包括局部放大图的制作。适用于多条曲线绘制在同一个图表中,但由于数据量过大,导致曲线的细节看不清,需要对细节进行局部放大。如下图: 环境准备 首先,确保你的Python环境中已经安装了以…

PHP超详细安装及应用

目录 所需安装包如下 一、PHP安装 依赖包安装 安装扩展工具(先将PHP所需的软件包全部拖进centos根目录下) 安装libmcrypt 安装mhash 安装mcrypt 安装PHP 二、设置LAMP组件环境(要保证mysql、http都安装完成了) Php.ini的建…

附录二-nmap基本用法

参考 黑客工具—Nmap的使用_哔哩哔哩_bilibili nmap是扫描IP和端口的,相当于攻击前的索敌步骤。不止网络安全方面会用到,平时运维的时候也会用到nmap 1 下载nmap nmap官网 https://nmap.org/ 点击下载,然后点你用的平台就行了 往下滚可以…

Linux环境在非root用户中搭建(java-tomcat-redis)

注: 本文在内网(离线)环境,堡垒机中搭建,服务器不同可能有所差异,仅供参考 本文安装JDK-20.0.1版本,apache-tomcat-10.1.10版本,redis-6.2.15版本 本文服务器IP假设:192.168.88.133 root用户创建子用户并…

stack overflow复现

当你在内存的栈中,存放了太多元素,就有可能在造成 stack overflow这个问题。 今天看看如何复现这个问题。 下图,是我写的程序,不断的创造1KB的栈,来看看执行了多少次,无限循环。 最后结果是7929kB时, 发…

Echarts 可视化图库案例(Make A Pie)

1、Made A Pie Made A Pie 2、可视化社区 (Made A Pie 替代) 可视化社区

标准价与移动平均价简介

一、移动平均价 移动平均价优点: a.移动平均价格可反应”实时的”加权平均价格,特别是物料价格涨跌幅度大时物料的价格不会被差异扭曲。 b.因为是基于交易的实时加权平均计算价格,一般情况下,移动平均价不产生差异,价格相对真实。 c.如果所有的物料都使用…