如何做某网站的移动客户端开发,wordpress文字可以动的插件,网站制作费用 厦门,哪个网站可以查公司注册信息instead of 触发器 专栏内容#xff1a; postgresql使用入门基础手写数据库toadb并发编程 个人主页#xff1a;我的主页 管理社区#xff1a;开源数据库 座右铭#xff1a;天行健#xff0c;君子以自强不息#xff1b;地势坤#xff0c;君子以厚德载物. 文章目录 inst…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 $$
BEGINIF (TG_OP DELETE) THENDELETE 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) THENUPDATE 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) THENINSERT 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_auditINSTEAD OF INSERT OR UPDATE OR DELETE ON emp_viewFOR EACH ROW EXECUTE FUNCTION update_emp_view();结果展示
测试一下审计模块的效果。
信息查看
员工管理系统中对于薪资表结构如下
postgres \d emp_viewView senlleng.emp_viewColumn | 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.829596I | senllang | wangguo | 8000 | 2024-06-06 08:13:24.125127
(2 rows)
有两名新员工入职可以看到是那个操作员录入的并且当前录入的时间信息都可以看到。
变岗调薪
当人员调岗时对应的薪资也会发生变化
或者人员离职时需要删除对应的记录。
postgres update emp_view set salary 7500 where empnamewangguo;
UPDATE 1
postgres delete from emp_view where empnamezhanglei;
DELETE 1
postgres select * from emp_audit ;operation | userid | empname | salary | stamp
-------------------------------------------------------------------I | senllang | zhanglei | 10000 | 2024-06-06 08:13:05.829596I | senllang | wangguo | 8000 | 2024-06-06 08:13:24.125127U | senllang | wangguo | 7500 | 2024-06-06 08:14:30.737416D | senllang | zhanglei | 10000 | 2024-06-06 08:14:53.089083
(4 rows)
当然审计表的权限是非常高的只有在审计系统中才能查看而且审计数据是不能删除的。
总结 在视图上的instead of 触发器可以将原本的执行计划重写 替换为触发器执行这样可以进行更为复杂的动作这里以审计为例演示了触发器的效果。
结尾 非常感谢大家的支持在浏览的同时别忘了留下您宝贵的评论如果觉得值得鼓励请点赞收藏我会更加努力 作者邮箱studysenllang.onaliyun.com 如有错误或者疏漏欢迎指出互相学习。
注未经同意不得转载
