inserted & deleted 中间表

功能描述

Vastbase 在 SQL Server 兼容模式下，支持在对表创建的语句级after触发器所关联的触发器函数中，查询inserted 和 deleted中间表。其中：

删除的表(deleted)会存储 DELETE 或 UPDATE 语句更改行之前触发器表中这些受影响的行的副本（触发器表是运行 DML 触发器的表）。在执行 DELETE 或 UPDATE 语句期间，会先从触发器表中复制受影响的行，然后将这些行传输到删除的表(deleted)中。
插入的表(inserted)会存储在 INSERT 或 UPDATE 语句之后出现的新行或更改的行的副本。在执行 INSERT 或 UPDATE 语句期间，触发器表中的新行或更改的行会复制到插入的表(inserted)中，即插入的表中存储被当前SQL所插入或更新的行的集合。

删除的表(deleted)和插入的表(inserted)被自动创建和管理，用户可以通过这两种驻留内存的临时表来测试特定数据修改的影响以及设置 DML 触发器操作条件，但不能直接修改表中的数据或对表执行数据定义语言 (DDL) 操作，例如 CREATE INDEX。

deleted和inserted中间表用途举例

例如在UPDATE的执行过程中，等同于先后进行了删除和插入操作，在此期间UPDATE事务会对deleted和inserted中间表进行如下处理：

1、原始行从触发器表复制到删除的表中。

2、触发器表会使用来自 UPDATE 语句的新值进行更新。

3、触发器表中的更新行将复制到插入的表中。

这样就能将更新之前行的内容（在删除的表中）与更新后的新行（在插入的表中）进行比较。

注意事项

该功能仅在数据库兼容模式为SQL Server时支持（即数据库实例初始化时指定DBCOMPATIBILITY='MSSQL'）。
当开启vbplsql_check参数（默认为关闭）时，由于 inserted 和 deleted 中间表在数据库中实际并不存在，此时会报错提示表不存在，导致创建触发器失败。
inserted 和 deleted 中间表结果集仅存在于 after 触发的表级 for statement定义的触发器。
INSERT、UPDATE、DELETE三个触发动作均为表级触发，不考虑列级触发。
inserted 和 deleted 中间表不支持的场景包括：
- 嵌套和递归场景；
- INSERT 的 ON DUPLICATE|CONFLICT KEY UPDATE 语法对触发器的触发场景；
- 主外键级联删除与级联更新场景；
- truncate事件；
- 表的约束触发器、when condition (即创建触发器时指定when条件)；
- 分区表、列存表、临时表、unlogged表、外表。

示例

1、创建测试表。

CREATE TABLE emp_1200424(
emp_1200424name text,
salary integer
);

INSERT INTO emp_1200424 VALUES('中间表测试 ',12345);
INSERT INTO emp_1200424 VALUES('中间表测试11111 ',1);

CREATE TABLE emp_1200424_old(
emp_1200424name text,
salary integer
);

INSERT INTO emp_1200424_old VALUES('中间表测试 ',12345);
INSERT INTO emp_1200424_old VALUES('中间表测试11111 ',1);

CREATE TABLE emp_1200424_new(
emp_1200424name text,
salary integer
);

CREATE TABLE emp_1200424_audit(
operation char(1),
stamp timestamp,
userid text,
emp_1200424name text,
salary integer
);

2、创建触发器函数，其中对inserted、deleted中间表的数据进行查询处理，并外化在了old、new表中。

CREATE OR REPLACE FUNCTION process_emp_1200424_new() RETURNS TRIGGER AS $emp_1200424_old_new$
BEGIN
insert into emp_1200424_audit select 'U', now(), current_user, n.* from inserted n;
update emp_1200424_old set salary = 0 where emp_1200424name in (select emp_1200424name from deleted);
insert into emp_1200424_new select * from inserted;
return null;
end;
$emp_1200424_old_new$ language plpgsql;

3、创建触发器。

CREATE TRIGGER emp_1200424_after_upd_statement
AFTER UPDATE ON emp_1200424 for each statement EXECUTE PROCEDURE process_emp_1200424_new();

4、更新测试表数据，以触发trigger。

UPDATE emp_1200424 SET salary=20 WHERE salary = 1;

5、查询表数据。

SELECT * FROM emp_1200424;
SELECT * FROM emp_1200424_old;
SELECT * FROM emp_1200424_new;
SELECT * FROM emp_1200424_audit;

返回结果为：

 emp_1200424name  | salary
------------------+--------
 中间表测试       |  12345
 中间表测试11111  |     20
(2 rows)

 emp_1200424name  | salary
------------------+--------
 中间表测试       |  12345
 中间表测试11111  |      0
(2 rows)

 emp_1200424name  | salary
------------------+--------
 中间表测试11111  |     20
(1 row)

 operation |           stamp            | userid | emp_1200424name  | salary
-----------+----------------------------+--------+------------------+--------
 U         | 2024-05-28 15:23:17.672179 | vbss   | 中间表测试11111  |     20
(1 row)

6、清理环境。

DROP TRIGGER emp_1200424_after_upd_statement ON emp_1200424;
DROP TABLE emp_1200424;
DROP TABLE emp_1200424_old;
DROP TABLE emp_1200424_new;
DROP TABLE emp_1200424_audit; 
DROP FUNCTION process_emp_1200424_new;

VastbaseG100

inserted & deleted 中间表

功能描述

注意事项

示例

弹框头部