创建 DDL 触发器

本节主要介绍创建 DDL 触发器的方式。

注意事项

只有超级用户才能创建事件触发器。
如果 DDL 命令出现错误失败，将不会执行 ddl_command_end 触发器。
如果 ddl_command_start 触发器出现错误失败，将不会引发 DDL 触发器，并且不会尝试执行该命令。类似地，如果一个 ddl_command_end 触发器出现错误失败，DDL 命令的效果将被回滚，就像其他包含事务中止的情况中那样。
如果事件上指定了多个 DDL 触发器，那么当触发该事件相关的 DDL 触发器时，会根据 DDL 触发器的名称的字母序升序方式来调用这些触发器。
仅 Vastbase G100 V2.2 Build 10(Patch No.12)及以后补丁版本支持 DDL 触发器。

语法格式

CREATE EVENT TRIGGER name
    ON event [ ON DATABASE | SCHEMA ]
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE PROCEDURE function_name()

CREATE EVENT TRIGGER name
    { AFTER { LOGON | STARTUP } | BEFORE { LOGOFF | SHUTDOWN } } ON DATABASE
    EXECUTE PROCEDURE function_name()

参数说明

name

DDL 触发器名称。在该数据库中这个名称必须唯一。
ON DATABASE

表示 DDL 触发器绑定于 database 对象，LOGON 与 LOGOFF 事件仅支持指定 ON DATABASE。
ON SCHEMA

表示 DDL 触发器绑定于 schema 对象。
event

会触发对给定函数调用的事件名称，分别为 ddl_command_start、ddl_command_end、sql_drop、table_rewrite、LOGON、LOGOFF、STARTUP 和 SHUTDOWN。
- ddl_command_start：在 CREATE、ALTER、DROP、SECURITY LABEL、COMMENT、GRANT 或 REVOKE 命令执行前触发。
- ddl_command_end：在 CREATE、ALTER、DROP、SECURITY LABEL、COMMENT、GRANT 或 REVOKE 命令执行后触发。
- sql_drop：为任何删除数据库对象的操作在 ddl_command_end DDL 触发器之前触发。
- table_rewrite：在表被命令ALTER TABLE和ALTER TYPE的某些动作重写之前触发。
- LOGON：用户登录后触发，仅支持 AFTER LOGON。
- LOGOFF：用户登出前触发，仅支持 BEFORE LOGOFF。
- STARTUP：启动数据库后触发，仅支持 AFTER STARTUP。
- SHUTDOW：停止数据库前触发，仅支持 BEFORE SHUTDOWN。

仅 postgres 系统库支持 BEFORE SHUTDOWN。

主备集群的备节点不支持 BEFORE SHUTDOWN。

filter_variable

用来过滤事件的变量名称。这可以用来限制触发器的触发事件。当前仅支持通过 TAG 过滤。

TAG 是 SQL 命令执行后的客户端的返回值，例如执行 SQL 语句 CREATE TABLE fruit (id INTEGER)； ，则返回的 TAG 为 CREATE TABLE。
filter_value

与该触发器要为其引发的 filter_variable 相关联的一个值列表。对于 TAG 这表示一个命令标签列表（例如 DROP FUNCTION）。
function_name

一个用户提供的函数。触发器函数需要声明为无参数，且返回类型为 Event Trigger 的函数。

示例

示例1： 创建 ddl_command_start 类型的 DDL 触发器，指定多条命令触发。

1、创建 DDL 触发器函数。

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

2、创建 DDL 触发器。

   CREATE EVENT TRIGGER estart ON ddl_command_start WHEN TAG IN ('CREATE TABLE','ALTER TABLE','DROP TABLE') EXECUTE PROCEDURE eth1();

3、执行 DDL 触发器中指定命令CREATE TABLE。

   CREATE TABLE  t_eventtr(id int);

结果返回如下：

   NOTICE:  test_event_trigger: ddl_command_start CREATE TABLE
   CREATE TABLE

4、执行 DDL 触发器中指定命令ALTER TABLE。

   ALTER TABLE t_eventtr RENAME TO t_eventtr_new;

结果返回如下：

   NOTICE:  test_event_trigger: ddl_command_start ALTER TABLE
   ALTER TABLE

5、执行 DDL 触发器中指定命令DROP TABLE。

   DROP TABLE t_eventtr_new;

结果返回如下：

   NOTICE:  test_event_trigger: ddl_command_start DROP TABLE
   DROP TABLE

示例2： 创建 ddl_command_end 类型的 DDL 触发器。

1、创建 DDL 触发器函数。

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

2、创建测试表t_eventtr。

   CREATE TABLE t_eventtr(id int);

返回结果如下：

   NOTICE:  test_event_trigger: ddl_command_start CREATE TABLE
   CREATE TABLE

3、创建 DDL 触发器。

   CREATE EVENT TRIGGER eend ON ddl_command_end EXECUTE PROCEDURE  eth1();

4、删除测试表t_eventtr。

   DROP TABLE t_eventtr;

结果返回如下：

   NOTICE:  test_event_trigger: ddl_command_end DROP TABLE
   DROP TABLE

示例3： 创建 sql_drop 类型的 DDL 触发器。

1、创建 DDL 触发器函数。

   CREATE OR REPLACE FUNCTION test_event_trigger_drop()
      RETURNS event_trigger LANGUAGE plpgsql AS $$
      DECLARE
         obj record;
      BEGIN
         FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
         LOOP
            RAISE NOTICE '% dropped object: % %.% %',
            tg_tag,
            obj.object_type,
            obj.schema_name,
            obj.object_name,
            obj.object_identity;
         END LOOP;
      END;
   $$;

2、创建测试表t_eventtr。

   CREATE TABLE t_eventtr(id int);

3、创建 DDL 触发器edrop。

   CREATE EVENT TRIGGER edrop ON sql_drop EXECUTE PROCEDURE test_event_trigger_drop();

4、删除表t_eventtr。

   DROP TABLE t_eventtr;

结果返回如下：

   NOTICE:  DROP TABLE dropped object: table public.t_eventtr public.t_eventtr
   NOTICE:  DROP TABLE dropped object: type public.t_eventtr public.t_eventtr
   NOTICE:  DROP TABLE dropped object: type public._t_eventtr public.t_eventtr[]
   DROP TABLE

示例4： 创建 table_rewrite 类型的 DDL 触发器。

1、创建 DDL 触发器函数。

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

2、创建 DDL 触发器。

   CREATE EVENT TRIGGER erewrite ON table_rewrite EXECUTE PROCEDURE eth1();

示例5： 创建 Logon 与 Logoff 类型的 DDL 触发器。

1、创建测试表test2。

create table test2(id int,col timestamp);

2、创建触发器函数eth3()和eth4()。

CREATE OR REPLACE FUNCTION eth3()
   RETURNS event_trigger
   LANGUAGE plpgsql
   AS $$ BEGIN
      insert into test2 values(1,systimestamp);
   END;
$$;

CREATE OR REPLACE FUNCTION eth4()
   RETURNS event_trigger
   LANGUAGE plpgsql
   AS $$ BEGIN
      insert into test2 values(2,systimestamp);
   END;
$$;

3、创建 Logon/Logoff DDL 触发器。

   CREATE EVENT TRIGGER logon_eventtri after Logon ON database 
      EXECUTE PROCEDURE eth3();

   CREATE EVENT TRIGGER logoff_eventtri before Logoff ON database 
      EXECUTE PROCEDURE eth4();

4、退出数据库重新登陆。

\q

退出后在命令行执行登陆命令。

   vsql -r

5、查询test2中记录的Logon/Logoff事件。

   SELECT * FROM test2;

结果返回如下：

    id |         col
   ----+---------------------
     2 | 2023-02-16 11:39:30
     1 | 2023-02-16 11:39:32
   (2 rows)

示例6： 创建 STARTUP 与 SHUTDOWN 类型的 DDL 触发器。

1、创建测试表：

   \c postgres
   DROP TABLE IF EXISTS test1;
   CREATE TABLE test1(id int,col timestamp);

2、创建事件触发器函数：

   CREATE OR REPLACE FUNCTION eth1()
   RETURNS event_trigger
   LANGUAGE plpgsql
   AS $$ BEGIN
   insert into test1 values(1,systimestamp);
   END;
   $$;
   CREATE OR REPLACE FUNCTION eth2()
   RETURNS event_trigger
   LANGUAGE plpgsql
   AS $$ BEGIN
   insert into test1 values(2,systimestamp);
   END;
   $$;

3、创建事件触发器：

   CREATE EVENT TRIGGER startup_eventtri after startup ON database EXECUTE PROCEDURE eth1();
   CREATE EVENT TRIGGER shutdown_eventtri before shutdown ON database EXECUTE PROCEDURE eth2();

4、重启数据库：

vb_ctl stop
vb_ctl start

或者

vb_ctl restart

启停数据库会导致业务中断，请在业务低峰期或者在业务停止期间进行。

5、查看表数据：

   vsql -d postgres
   SELECT * FROM test1;

查询结果如下：

 id | col
 ---+---------------------
  2 | 2024-02-05 18:50:00
  1 | 2024-02-05 18:50:05
(2 row)

VastbaseG100

创建 DDL 触发器

注意事项

语法格式

参数说明

示例

弹框头部