触发器支持自治事务

Vastbase G100 支持在触发器函数中使用自治事务。

自治事务是在定义PL/pgSQL定义函数/存储过程中，在声明部分增加指令：PRAGMA AUTONOMOUS_TRANSACTION 的函数。

本文主要介绍在触发器函数中使用自治事务的方式与示例，有关自治事务的详细信息，请参见自治事务。

注意事项

• 本文档提及的特性仅在数据库兼容模式为 Oracle 与 PostgreSQL 时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'或'PG'）。

• 暂不支持 TRUNCATE 触发器使用自治事务。

• 支持版本：

  • Vastbase G100 V2.2 Build 15 (Patch No.4)及以上补丁版本。

  • Vastbase G100 V2.2 Build 17 及以上版本。

语法格式

PRAGMA AUTONOMOUS_TRANSACTION;

示例

示例1： 多会话中带有自治事务查询结果与无自治事务的差异。

1、创建表。

CREATE TABLE test_trigger_src_tbl_1224528(id1 INT PRIMARY KEY, id2 INT, id3 INT);
CREATE TABLE test_trigger_des_tbl_1224528(id1 INT PRIMARY KEY, id2 INT, id3 INT);
INSERT INTO test_trigger_des_tbl_1224528 VALUES(1,2,3);
INSERT INTO test_trigger_des_tbl_1224528 VALUES(5,12,23);
INSERT INTO test_trigger_src_tbl_1224528 VALUES(91,2,3);
INSERT INTO test_trigger_src_tbl_1224528 VALUES(95,12,23);

2、创建 DML 触发器。

CREATE OR REPLACE FUNCTION tri_insert_func_1224528() RETURNS TRIGGER AS
$$
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_trigger_des_tbl_1224528 VALUES(new.id1, new.id2, new.id3);
RETURN new;
END
$$ LANGUAGE PLpgSQL;

CREATE TRIGGER insert_trigger_1224528
BEFORE INSERT ON test_trigger_src_tbl_1224528
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func_1224528();

3、在会话 1 中触发触发器。

BEGIN;
INSERT INTO test_trigger_src_tbl_1224528 VALUES(100,200,300);

4、在会话 2 中查询表数据。

SELECT * FROM test_trigger_src_tbl_1224528;
SELECT * FROM test_trigger_des_tbl_1224528;

返回结果为：

 id1 | id2 | id3
-----+-----+-----
  91 |   2 |   3
  95 |  12 |  23
(2 rows)

 id1 | id2 | id3
-----+-----+-----
   1 |   2 |   3
   5 |  12 |  23
 100 | 200 | 300
 (3 rows)

自治事务执行完成后就会提交，不会等待主事务提交，因此即使会话1中事务未提交，也能在会话2中查询到自治事务的修改。

5、切换回会话1，清理测试表与触发器。

ROLLBACK;
DROP TRIGGER insert_trigger_1224528 ON test_trigger_src_tbl_1224528;
DROP FUNCTION tri_insert_func_1224528;
DROP TABLE test_trigger_src_tbl_1224528;
DROP TABLE test_trigger_des_tbl_1224528;

示例2： 原表带有虚拟列，使用 BEFORE 触发器插入其他列。

1、创建测试表。

CREATE TABLE old_1196075(col1 INT,col2 INT GENERATED ALWAYS AS (col1 / 2) STORED);
CREATE TABLE tab_1196075(id INT,c1 INT);
INSERT INTO old_1196075 VALUES(1),(2);

2、创建 DML 触发器。

CREATE OR REPLACE FUNCTION tri_insert_func_1196075() RETURNS TRIGGER AS
$$
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO tab_1196075 VALUES (new.col1::INT,new.col2::VARCHAR);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_trigger_1196075
BEFORE INSERT ON old_1196075
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func_1196075();

3、向 old_1196075 表中插入数据，触发执行函数 tri_insert_func_1196075()。

INSERT INTO old_1196075 VALUES(10),(20);

4、分别查询表 old_1196075 与 tab_1196075。

SELECT * FROM old_1196075 ORDER BY 1,2;
SELECT * FROM tab_1196075 ORDER BY 1,2;

返回结果为：

 col1 | col2
------+------
    1 |    0
    2 |    1
   10 |    5
   20 |   10
(4 rows)

 id | c1
----+----
 10 |
 20 |
(2 rows)

5、清理测试表与触发器。

DROP TRIGGER IF EXISTS insert_trigger_1196075 ON old_1196075;
DROP FUNCTION IF EXISTS tri_insert_func_1196075;
DROP TABLE IF EXISTS old_1196075;
DROP TABLE IF EXISTS tab_1196075;

示例 3： 在 DDL 触发器中使用自治事务。

1、创建 DDL 触发器。

CREATE OR REPLACE FUNCTION eth1_1196400()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
PRAGMA autonomous_transaction;
BEGIN
RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END;
$$;

CREATE EVENT TRIGGER start_eventtri_1196400 ON ddl_command_start EXECUTE PROCEDURE eth1_1196400();
CREATE EVENT TRIGGER end_eventtri_1196400 ON ddl_command_end EXECUTE PROCEDURE eth1_1196400();
CREATE EVENT TRIGGER drop_eventtri_1196400 ON sql_drop EXECUTE PROCEDURE eth1_1196400();
CREATE EVENT TRIGGER rewrite_eventtri_1196400 ON table_rewrite EXECUTE PROCEDURE eth1_1196400();

2、创建表，触发执行函数 eth1_1196400()。

CREATE TABLE tb1_1196400(id SERIAL PRIMARY KEY, foo FLOAT);
INSERT INTO tb1_1196400 SELECT x * 1.001 FROM generate_series(1,500) AS t(x);

返回结果为：

OTICE:  test_event_trigger: ddl_command_start CREATE TABLE
NOTICE:  CREATE TABLE will create implicit sequence "tb1_1196400_id_seq" for serial column "tb1_1196400.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tb1_1196400_pkey" for table "tb1_1196400"
NOTICE:  test_event_trigger: ddl_command_end CREATE TABLE
CREATE TABLE

3、修改表列类型，触发执行函数 eth1_1196400()。

ALTER TABLE tb1_1196400 ALTER column foo TYPE NUMERIC;

返回结果为：

test_event_trigger: ddl_command_start,ALTER TABLE
test_event_trigger: ddl_command_end,ALTER TABLE
test_event_trigger: table_rewrite,ALTER TABLE

4、删除表，触发执行函数 eth1_1196400()。

DROP TABLE tb1_1196400;

返回结果为：

test_event_trigger: ddl_command_start,DROP TABLE
test_event_trigger: sql_drop,DROP TABLE
test_event_trigger: ddl_command_end,DROP TABLE

5、清理测试函数与 DDL 触发器。

DROP EVENT TRIGGER start_eventtri_1196400;
DROP EVENT TRIGGER end_eventtri_1196400;
DROP EVENT TRIGGER drop_eventtri_1196400;
DROP EVENT TRIGGER rewrite_eventtri_1196400;
DROP FUNCTION eth1_1196400;