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

instead of 触发器

​专栏内容：

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

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

文章目录

概述

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

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

机制原理

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

INSTEAD OF 触发器语法

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

CREATETRIGGER trigger_name
INSTEAD OF[INSERTORUPDATEORDELETE]ON table_name
FOR EACH ROWEXECUTEFUNCTION 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， 记录操作类型，操作员，数据变动，以及时间。

CREATETABLE emp (
  empname textPRIMARYKEY,
  salary integer);CREATETABLE emp_audit(
  operation char(1)NOTNULL,
  userid textNOTNULL,
  empname textNOTNULL,
  salary integer,
  stamp timestampNOTNULL);

创建视图

CREATEVIEW emp_view ASSELECT e.empname,
    e.salary,max(ea.stamp)AS last_updated
FROM emp e
LEFTJOIN emp_audit ea ON ea.empname = e.empname
GROUPBY1,2;

创建执行函数

• 在delete时，先删除基础表的数据；如果删除成功，再插入审计表，操作类型为’D’，否则不操作审计表；
• 在update时，同上，先更新基础表；如果成功，才插入审计表；
• 在insert时，先插入基础表；然后操作审计表；
• 每次都将操作时间更新为当前时间；

CREATEORREPLACEFUNCTION update_emp_view()RETURNSTRIGGERAS $$
BEGINIF(TG_OP ='DELETE')THENDELETEFROM emp WHERE empname = OLD.empname;IFNOT FOUND THENRETURNNULL;ENDIF;

      OLD.last_updated =now();INSERTINTO emp_audit VALUES('D',current_user, OLD.*);RETURN OLD;
  ELSIF (TG_OP ='UPDATE')THENUPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;IFNOT FOUND THENRETURNNULL;ENDIF;

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

创建触发器

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

CREATETRIGGER emp_audit
  INSTEAD OFINSERTORUPDATEORDELETEON emp_view
  FOR EACH ROWEXECUTEFUNCTION 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 01postgres=> insert into emp_view values('wangguo', 8000);
INSERT 01postgres=>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 1postgres=> delete from emp_view where empname='zhanglei';
DELETE 1postgres=>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
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！