postgresql 触发器知识点

最新推荐文章于 2023-10-09 21:50:03 发布
最新推荐文章于 2023-10-09 21:50:03 发布
阅读量811 收藏 1
点赞数
分类专栏： postgresql 文章标签： postgresql的服务器编程 触发器函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Gzigithub/article/details/89874245
版权

1、审核触发器

触发器最常见的用途之一是采用前后一致且透明的方式向表中记录数据的变化。当创建一个审核触发器时，首先我们必须决定我们要记录的内容。

被记录的事件的逻辑为：谁改变了数据，数据什么时候被改变了，什么操作改变了数据。这些信息可以用下面的表进行保存：

CREATE TABLE audit_log(
                username text, -- who did the change
                event_time_utc timestamp,  --when the event was recorded
                table_name text,  -- contains schema-qualified table name
                operation text,  -- insert, update, delete or truncate
                before_value json,  -- the OLD tuple value
                after_value json);  -- the NEW tuple value

接下来编写触发器函数：

CREATE or REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    old_row json := NULL;
    new_row json := NULL;
BEGIN
        IF TG_OP in ('UPDATE', 'DELETE') THEN
            old_row = row_to_json(OLD);
        END IF;
        IF TG_OP in ('INSERT', 'UPDATE') THEN
            new_row = row_to_json(NEW);
        END IF;
        INSERT INTO audit_log(username,event_time_utc,table_name,operation,before_value,after_value
        )VALUES (
        session_user,
        current_timestamp AT TIME ZONE 'UTC',
        TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
        TG_OP,
        old_row,
        new_row);
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建notify_test表

CREATE TABLE notify_test(i int)

函数编写完，准备定义一个新的日志触发器：

CREATE TRIGGER audit_log AFTER INSERT OR UPDATE or DELETE ON notify_test FOR EACH ROW EXECUTE PROCEDURE audit_trigger();

测试：

TRUNCATE notify_test;

INSERT INTO notify_test VALUES (1);

UPDATE notify_test SET i=2;

DELETE FROM notify_test;
 
SELECT * from audit_log;

2、无效的delete

如果我们的业务需求是数据只能在一些表中被添加和修改，但不能被删除，其中一种处理方法是从所有用户处撤销对这些表的delete操作（记得同时要从public处撤销delete），但是这也可以借助触发器来实现。

一个普通的取消触发器可以写成如下代码：

CREATE or REPLACE FUNCTION cancel_op()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_WHEN = 'AFTER' THEN
        RAISE EXCEPTION 'you are not allowed to % rows in %.%',
                TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME;
    END IF;
    raise notice '% on rows in %.% won''T HAPPEN',
                TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME;
     RETURN NULL;
END;
$$ LANGUAGE plpgsql;

对于before与after触发器，我们可以使用相同的触发器函数。如果你将其当作before触发器使用，那么操作会跳过一个消息，但如果你将其当作after触发器，那么就会引发错误，而且当前事务（子事务）就会回滚。

CREATE TABLE delete_test1(i int);

INSERT INTO delete_test1 VALUES(1);

CREATE TRIGGER disallow_delete AFTER DELETE on delete_test1 for EACH ROW EXECUTE PROCEDURE cancel_op();

DELETE from delete_test1 WHERE i =1;

[SQL]DELETE from delete_test1 WHERE i =1;
[Err] ERROR:  you are not allowed to DELETE rows in public.delete_test1
CONTEXT:  PL/pgSQL function cancel_op() line 4 at RAISE
（这里注意到after触发器抛出了一个错误信息）

CREATE TRIGGER skip_delete BEFORE DELETE on delete_test1 for EACH ROW EXECUTE PROCEDURE cancel_op();

DELETE from delete_test1 WHERE i =1;

[SQL]DELETE from delete_test1 WHERE i =1;
NOTICE:  DELETE on rows in public.delete_test1 won'T HAPPEN
CONTEXT:  PL/pgSQL function cancel_op() line 7 at RAISE

这一次，BEFORE触发器取消了DELETE和AFTER触发器，虽然任然在那里，但并未达到。

3、无效的TRUNCATE

你可能已经发现，如果你使用TRUNCATE来删除所有东西，之前的触发器就很容易被DELETE忽略掉。

尽管通过返回NULL（这个仅仅对行级别的BEFORE触发器有效），你可能不会轻易跳过TRUNCATE，但如果TRUNCATE被尝试，你还是无法抛出错误信息。使用之前DELETE使用过的相同函数，来创建AFTER触发器：

CREATE TRIGGER disallow_truncate AFTER TRUNCATE ON delete_test1 FOR EACH STATEMENT EXECUTE PROCEDURE cancel_op();


这时你再也无法使用TRUNCATE:
TRUNCATE delete_test1;
[SQL]TRUNCATE delete_test1;

[Err] ERROR:  you are not allowed to TRUNCATE rows in public.delete_test1
CONTEXT:  PL/pgSQL function cancel_op() line 4 at RAISE

当然，你可以在一个BEFORE触发器抛出错误，但之后，你需要编写你自己的不受限制的错误抛出触发器函数，来替代cancel_op()。

4、修改NEW记录

另一种常用的审核方式是在同一行的特定字段中，如同记录数据一样记录操作信息。例如，我们定义一个触发器，这个触发器可以在每个INSERT和UPDATE事务发生的时候，在字段last_changed_at与字段last_changed_by中记录操作时间与当前的用户。在行级别的BEFORE触发器里面，你可以通过变更NEW记录的方式，修改实际需要被写入数据库的内容。你可以将值分配给一些字段，甚至可以使用相同结构返回一个不同的记录。例如，如果你想通过UPDATE触发器返回OLD，你需要确保行没有被更新。

时间戳触发器

为了在表中创建审核记录的基础内容，我们创建一个触发器，记录最后做变动的用户以及变动的发生时间：

CREATE OR REPLACE FUNCTION changestamp()
  RETURNS TRIGGER AS $$
BEGIN
  NEW.last_changed_by = SESSION_USER;
  NEW.last_changed_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

当然，这个只对于有正确字段的表有效：

​
CREATE TABLE modify_test(
    id serial PRIMARY KEY,
    data text,
    created_by text default SESSION_USER,
    created_at timestamp default CURRENT_TIMESTAMP,
	last_changed_by text default SESSION_USER,
	last_changed_at timestamp default CURRENT_TIMESTAMP);

​
CREATE TRIGGER changestamp BEFORE UPDATE ON modify_test FOR EACH ROW EXECUTE PROCEDURE changestamp();

现在，让我们看一下我们刚创建的触发器：

INSERT INTO modify_test(data) VALUES('something');

UPDATE modify_test SET data='something else' WHERE id =1;

SELECT * FROM modify_test;

5、不可改变的字段触发器

如果你将行中的字段作为你审核记录的部分内容，那么你需要确保值反馈的是真实情况。我们曾经确保字段last_changed_*能够包含正确的值，但是created_by 与created_at这两个值是否正确呢？这些在后续的升级中能够轻易被修改，但它们不应该发生变动。甚至在刚开始的时候，它们可被错误的赋值，因为在insert语句中赋予任何其他值，默认值就会很容易被忽略。

所以，让我们把changestamp()触发器函数修改成一个usagestamp()函数，这样能确保初始值的准确性与稳定性：

CREATE or REPLACE FUNCTION usagestamp()
RETURNS TRIGGER AS $$
BEGIN
   if TG_OP = 'INSERT' THEN
     NEW.created_by = SESSION_USER;
     NEW.created_at = CURRENT_TIMESTAMP;
   ELSE
     NEW.created_by = OLD.created_by;
     NEW.created_at = OLD.created_at;
   END IF;
  
   NEW.last_changed_by = SESSION_USER;
   NEW.last_changed_at = CURRENT_TIMESTAMP;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

在insert这个例子中，我们将created_*字段设置成所需的值，忽视了insert查询尝试设置的值。而对于update，我们只是将继续使用旧值，继续忽略其他修改尝试。

这个函数接下来被用于创建一个BEFORE INSERT OR UPDATE触发器：

CREATE TRIGGER usagestamp BEFORE INSERT OR UPDATE ON modify_test FOR EACH ROW EXECUTE PROCEDURE usagestamp();

现在，让我们尝试更新创建的审计日志信息。首先，我们需要删除原有的触发器，这样同一张表就不会有两个触发器。然后，我们将试图改变created_by和created_at值：

DROP TRIGGER changestamp ON modify_test;

UPDATE modify_test SET created_by ='notpostgres',created_at='2019-05-08';

SELECT * from modify_test;

可以看出，创建的信息还是一样的，但是最近修改过的信息已经得到更新。

6、有条件的触发器

通用的WHEN子句是控制触发器的一种灵活方法，其与SQL查询中的WHERE相似。借助WHEN子句，你可以编写任何的表达式（除子查询），同时可以在触发器函数被调用之前测试表达式。表达式必须输出Boolean值，如果该值是FLASE（或者NULL，会自动转换成FALSE），触发器函数就不会被调用。

例如，你可以使用以下代码。来实施“周五下午禁止更新”的政策。

CREATE OR REPLACE FUNCTION cancel_with_message()
RETURNS TRIGGER AS $$
BEGIN
   raise EXCEPTION '%', TG_ARGV[0];
   RETURN NULl;
END;
$$ LANGUAGE plpgsql;

这个函数抛出一个异常情况，这个异常是通过create trigger 语句中的字符串参数进行传递的。请注意，我们不能直接将TG_ARGV[0]作为消息进行使用，因为PL/pgSQL的语法要求字符串常量作为raise的第三个元素。

借助前面的触发器函数，我们可以通过指定条件（在WHEN(....)子句中）和条件符合的情况下需要抛出的消息（作为触发器的参数），来设置触发器进而执行各种约束。

CREATE TRIGGER no_update_on_friday_afternoon BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON new_tasks FOR EACH STATEMENT
WHEN (CURRENT_TIME > '12:00' AND EXTRACT(DOW FROM CURRENT_TIMESTAMP)=5) EXECUTE PROCEDURE cancel_with_message('sorry, we have a
"NO task change on Friday afternoon" policy!');

如果在星期五的下午尝试去修改new_tasks表，他会得到关于这个政策的消息。

注意：关于触发器参数，其中需要注意的事情是参数列表总是一个文本数组（text[]）。create trigger语句中给出的所有参数都被转换成字符，这个也包括了任何的null值。这意味着，如果我们把null放入参数列表中，PG_ARGV中的相应插槽中会得到文本null。

7、传递给PL/pgSQL  TRIGGER函数的变量

对于数据库端的操作，触发器时一种合适的工具，例如审计、日志、执行复杂约束甚至是复制。然而，对于大多数应用程序逻辑，我们还是尽可能避免使用触发器，由于触发器会导致离奇却难以调试的问题

下面是一张完整使用PL/pgSQL编写的触发器函数变量列表：

OLD，NEWRECORD触发器调用的是before与after行图像。OLD未分配给INSERT，NEW未分配给DELETE 两者在语句级触发器中均未分配
TG_NAME name触发器的名称（来自触发器定义）
TG_WHENtext BEFORE、AFTER或者INSTEAD OF 之一
TG_LEVELtextROW或者STATEMENT
TG_OPtextINSERT、UPDATE、DELETE或者TRUNCATE之一
TG_RELIDoid触发器创建依赖表中的OID
TG_TABLE_NAMEname表的名称
TG_TABLE_SCHEMAname表架构的名称
TG_NARGS，TG_ARGV[] int,tetx[]触发器定义中的参数数量与参数数组

 

 

Glyann
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
postgresql使用触发器分表
phq12的博客
04-27 1349
分表的概念和好处请自行查阅，与其他方法相比的优劣也不赘述，这里只介绍一种分表的方法。 使用触发器分表：每月一张表，分表表名格式mainNameYYYYMM。 第一步：创建数据库存储过程，插入数据时按照规则插入不同分表。插入时如果违反分表的约束会报错。 CREATE OR REPLACE FUNCTION auto_insert_into_table() RETURNS trigger ...
进阶数据库系列（十）：PostgreSQL 视图与触发器
pgdba的博客
08-10 385
视图（View）本质上是一个存储在数据库中的查询语句。视图本身不包含数据，也被称为虚拟表。我们在创建视图时给它指定了一个名称，然后可以像表一样对其进行查询。触发器：一个触发器是一种声明，告诉数据库应该在执行特定的操作时执行特定的函数。触发器函数：是指一个没有参数并且返回trigger类型的函数。创建触发器创建一个触发器，使得每次该表有新数据insert时，其中的一个时间字段uptime自动变更为当前时间。创建一个自定义触发器函数，此函数的主要是为了更新当前时间。
PostgreSQL中的触发器
HighGO
04-05 1930
作者：瀚高PG实验室（HighgoPGLab） 丹心明月 注：本文章主要翻译自《PostgreSQL 13.0 Documentation》第三十八章 本章介绍编写函数触发器的相关信息。触发器函数可使用大部分可用的过程语言编写，包括PL/pgSQL（参见第42章），PL/Tcl（参见第43章），PL/Perl（参见第44章），和PL/Python（参见第45章）。阅读完本章后，可根据自己的喜好，到相应章节查看对应语言编写触发器的具体细节。 也可以使用C语言编写触发器，不过使用过程语言更简...
postgresql 删除触发器_Postgresql创建新增、删除与修改触发器的方法
weixin_35363895的博客
12-31 727
新增触发器第一步：创建函数为待模糊查询的表创建函数CREATE OR REPLACE FUNCTION fuzzy_query_func() RETURNS TRIGGER AS $$BEGININSERT INTO fuzzy_query(id,name,address,table_name) VALUES (new.id, NEW.name,NEW.address,TG_TABLE_NAME)...
PostgreSQL创建函数
linyou?
12-03 1466
PostgreSQL创建函数
PostgreSQL：十一. 触发器
数据知道的博客
05-10 3万+
十一. 触发器 11.1 什么是触发器和触发器函数 触发器：一个触发器是一种声明，告诉数据库应该在执行特定的操作时执行特定的函数。 触发器函数：是指一个没有参数并且返回trigger类型的函数。 11.2 创建触发器 【例11.1】创建一个触发器，使得每次该表有新数据insert时，其中的一个时间字段uptime自动变更为当前时间。 首先创建一个用于测试的数据表，SQL语句如下： CREATE TABLE timedb (uid INTEGER,gid INTEGER,uptime timestamp wi
postgresql 触发器
dahuzi的专栏
09-20 775
1 触发器概述 触发器的功能就是为了解决这类问题而设计的，当你更新或查询某个资料表时会触动触发器，触发器就会照您所设计的流程，同步去插入、更新、删除其他资料，你不再需要重复下达多次的SQL命令就能达成一连串资料的同步处理。 触发器是某个数据库操作发生时被自动调用的函数。可以在INSERT、UPDATE或DELETE操作之前或之后调用触发器。PostgreSQL支持两种类型的触发器，一种是数
C# 操作PostgreSQL 数据库的示例代码
08-28
知识点1：PostgreSQL 概述 PostgreSQL 是一个功能强大的开源对象关系数据库管理系统（ORDBMS），用于安全地存储数据，并支持最佳做法，允许在处理请求时检索它们。PostgreSQL 由PostgreSQL全球开发集团（全球志愿者...
django-tsvector-field：具有托管存储过程和触发器的tsvector（PostgreSQL全文搜索向量）的Django字段
02-05
**详细知识点：** 1. **Django框架**：Django是一个高级的Python Web框架，用于快速开发安全和可维护的网站。它提供了模型-视图-控制器（MVC）的设计模式，以及ORM（对象关系映射）来与数据库交互。 2. **...
顶级PostgreSQL DBA日常工作分享
03-24
然而，由于题目和描述都明确给出了“顶级PostgreSQL DBA日常工作分享”，我可以根据这个主题构建一些相关的知识点。 知识点： PostgreSQL数据库概述： PostgreSQL是一个开放源代码的对象关系数据库系统（ORDBMS）...
进阶数据库系列（十一）：PostgreSQL 存储过程
最新发布
pgdba的博客
10-09 3066
在 PostgreSQL 中，除了标准 SQL 语句之外，通过创建复杂的过程和函数来满足程序需要，我们称为存储过程和自定义函数（User-Defined Function）。它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。
postgresql之触发器
weixin_44847119的博客
08-28 8295
一，前人总结 1.触发器在指定的数据库事件发生时调用 2.语法 create trigger trigger_name {before(在执行操作之前触发)|after(在执行操作之后触发)|instead(更新操作 视图)} {event(insert,update,delete,truncate)} on table_name {for each row(对指定操作的每一行调用一次)|for each statement (只会为任何指定的操作执行一次，不管修改多少行)} execute procedu
oracle raise默认作用,postgreSQL之raise用法
weixin_34297395的博客
04-04 1234
raise函数在PostgreSQL中，该函数用于打印字符串，类似于Java中的System.out.println()，Oracle中的dbms_output.put_line()。在PostgreSQL中可以利用RAISE语句报告信息和抛出错误，其声明形式为：RAISE level 'format' [, expression [, ...]];1RAISElevel'format'[,exp...
postgreSQL触发器
qq_25775675的博客
09-05 5260
触发器是某个数据库操作发生时被**自动调用的函数**。可以在`INSERT`、`UPDATE`或`DELETE`操作之前或之后调用触发器。`PostgreSQL`支持两种类型的触发器，一种是**数据行级触发器**，另外一种是**语句级触发器**。对于数据行级的触发器，触发发触发器的语句每操作一个数据行，它就被执行一次。对于语句级的触发器，它只会被执行一次。
触发器详解
Think
08-31 4841
一个触发器是一种声明，告诉数据库应该在执行特定的操作的时候执行特定的函数。 触发器可以附加到表、视图和外部表上。 在表或外部表上，触发器可以定义在一个INSERT, UPDATE, 或 DELETE命令之前或者之后执行， 要么是对每行执行一次(for row)，要么是对每条SQL语句执行一次(for statement)。触发器可以为TRUNCATE语句触发。如果发生触发器事件，
PGSQL学习——触发器
Jon_Celoon的博客
09-03 4095
本文转自：https://blog.csdn.net/neo_liu0000/article/details/6255623，对其中内容略作调整。 重点摘录： 创建触发器以前，必须定义触发器使用的函数。这个函数不能有任何参数，它的返回值的类型必须是trigger。函数定义好以后，用命令CREATE TRIGGER创建触发器。多个触发器可以使用同一个函数。 语句级的触发器应该返回NULL。 如果同一表上同对同一个事件定义了多个触发器，这些...
PGSQL触发器实例
xcagy-国际性的文档记录中心
02-05 796
---创建触发器执行的函数 CREATE OR replace FUNCTION tri_assets_in_update() returns trigger as $$ begin IF (new.total_invt-OLD.total_invt) > 0 THEN insert into assets_in(code,ast_id,in_number,name,property...
PostgreSQL触发器
热门推荐
neo_liu0000的专栏
03-17 3万+
1 触发器概述        触发器是某个数据库操作发生时被自动调用的函数。可以在INSERT、UPDATE或DELETE操作之前或之后调用触发器。PostgreSQL支持两种类型的触发器，一种是数据行级触发器，另外一种是语句级触发器。对于数据行级的触发器，触发发触发器的语句每操作一个数据行，它就被执行一次。对于语句级的触发器，它只会被执行一次。         创建触发器以前，必须定义触发器使用的函数。这个函数不能有任何参数，它的返回值的类型必须是trigger。函数定义好以后，用命令CREATE TRI
PostgreSQL 触发器、函数
qq_35279788的博客
06-28 962
创建触发器、函数1、创建触发器create or replace function addfun() returns trigger as $addfun$beginRETURN NULL;end;$addfun$ LANGUAGE pluxsql;CREATE TRIGGER triggername AFTER INSERT OR UPDATE OR DELETE ON mytabll FOR ...
postgresql数据库知识
07-28
PostgreSQL是一个强大的开源对象关系数据库系统，它使用并扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。它可以在所有主要操作系统上运行，并具有高度的可扩展性。PostgreSQL支持多种数据类型，包括数字、字符串、日期、数组、JSON、XML等。它还支持SQL的许多功能，如复杂查询、外键、触发器、视图、事务等。此外，PostgreSQL还具有许多高级功能，如索引优化、多版本并发控制(MVCC)、分区、安全性、可靠性和可扩展性等。它还支持多种编程接口，如C/C++、Java、Perl、Python、Ruby、Tcl和ODBC。总之，PostgreSQL是一个功能强大且灵活的数据库系统，适用于各种应用场景。\[2\]\[3\] #### 引用[.reference_title] - *1* *2* [PostgreSQL数据库基础知识](https://blog.csdn.net/weixin_30647065/article/details/101528775)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *3* [PostgreSQL知识点](https://blog.csdn.net/ZHENhen_520/article/details/122156419)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值