迁移 Oracle 到 PostgreSQL: 触发器

七月 29, 2023

摘要：触发器是存储在数据库中并在发生指定事件时触发的过程。导致触发器运行的关联事件可以绑定到特定的数据库表、数据库视图、数据库模式或数据库本身。

Oracle 用法

触发器可以在以下之后运行：

  • 数据操作语言（DML）语句，如DELETE、INSERT或UPDATE。
  • 数据定义语言（DDL）语句，如CREATE、ALTER或DROP。
  • 数据库事件和操作，如SERVERERROR、LOGON、LOGOFF、STARTUP或SHUTDOWN。

触发器类型

  • DML 触发器可以在表或视图上创建，并在插入、更新或删除数据时触发。触发器可以在 DML 命令运行之前或之后触发。
  • 可以在不可编辑的视图上创建 INSTEAD OF 触发器。INSTEAD OF触发器提供了一种应用程序透明的方法，用于修改无法由 DML 语句修改的视图。
  • SYSTEM 事件触发器在数据库或架构级别定义，包括在特定事件后触发的触发器：
    • 用户登录和注销。
    • 数据库事件（启动/关闭）、DataGuard 事件、服务器错误。

例子

创建一个触发器，该触发器在从表PROJECTS中删除行后运行，或者在项目的主键更新时运行。

CREATE OR REPLACE TRIGGER PROJECTS_SET_NULL
  AFTER DELETE OR UPDATE OF PROJECTNO ON PROJECTS
  FOR EACH ROW
  BEGIN
    IF UPDATING AND :OLD.PROJECTNO != :NEW.PROJECTNO OR DELETING THEN
      UPDATE EMP SET EMP.PROJECTNO = NULL
      WHERE EMP.PROJECTNO = :OLD.PROJECTNO;
    END IF;
END;
/

Trigger created.

DELETE FROM PROJECTS WHERE PROJECTNO=123;

SELECT PROJECTNO FROM EMP WHERE PROJECTNO=123;

PROJECTNO
NULL

在表上创建系统/模式触发器。如果为HR模式中的对象运行DROP类型的 DDL 命令，则触发器将触发。它可以防止丢弃对象并引发应用程序错误。

CREATE OR REPLACE TRIGGER PREVENT_DROP_TRIGGER
  BEFORE DROP ON HR.SCHEMA
BEGIN
    RAISE_APPLICATION_ERROR (num => -20000,
    msg => 'Cannot drop object');
END;
/

Trigger created.

DROP TABLE HR.EMP

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot drop object
ORA-06512: at line 2

有关详细信息，请参阅 Oracle 文档中的 CREATE TRIGGER 语句。

PostgreSQL 用法

触发器是存储在数据库中并在发生指定事件时触发的过程。PostgreSQL 中的 DML 触发器共享 Oracle 触发器中存在的许多功能。

  • DML 触发器（基于与表相关的事件（如 DML）触发的触发器）。
  • 事件触发器（在某些数据库事件（如运行 DDL 命令）之后触发的触发器）。

与 Oracle 触发器不同，PostgreSQL 触发器必须调用函数，并且不支持将 PL/pgSQL 代码的匿名块作为触发器主体的一部分。用户提供的函数声明时不带参数，并且返回类型为触发器。

PostgreSQL 创建触发器概要

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]}
  ON table_name
  [ FROM referenced_table_name ]
  [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
  [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
  [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:
  INSERT
  UPDATE [ OF column_name [, ... ] ]
  DELETE
  TRUNCATE

注意

REFERENCING是 PostgreSQL 10 中引入的新选项。可以将此选项与AFTER触发器一起使用，以与OLD或NEW TABLE更改行的整体视图进行交互。

在某些情况下，可以多次触发多个触发器。这包括未计划运行的触发器，例如：

  • 带有ON CONFLICT DO UPDATE子句的INSERT语句可能会导致插入和更新操作触发。
  • 由外键强制导致UPDATE或DELETE可以触发触发器。例如，ON UPDATE CASCADE或者ON DELETE SET NULL可以触发应该由表上的UPDATE或DELETE命令触发的触发器。

PostgreSQL DML 触发器

PostgreSQL触发器可以在 DML 操作之前或之后运行。

  • 在尝试操作一行数据之前触发。
    • 在约束检查之前，并尝试INSERT、UPDATE或DELETE时。
    • 如果触发器在事件之前或代替事件触发，则触发器可以跳过当前行的操作或更改要插入的行（仅限INSERT和UPDATE操作）。
  • 操作完成后，在约束检查完并完成INSERT、UPDATE或DELETE命令之后。如果触发器在事件发生后触发，则触发器可以看到所有更改，包括其他触发器的效果。

PostgreSQL 触发器可以在视图上创建时运行INSTEAD OF形式的 DML 命令。

PostgreSQL 触发器可以针对受 DML 语句影响的每行记录（FOR EACH ROW）运行，也可以作为 DML 语句的一部分只运行一次（FOR EACH STATEMENT）。

触发时间 数据库事件 行级触发器 语句级触发器
之前 INSERT, UPDATE, DELETE 表和外表 表、视图和外部表
之前 TRUNCATE 不适用 表
之后 INSERT, UPDATE, DELETE 表和外表 表、视图和外部表
之后 TRUNCATE 不适用 表
替代执行 INSERT, UPDATE, DELETE 视图 不适用
替代执行 TRUNCATE 不适用 不适用

PostgreSQL 事件触发器

当数据库中发生与触发器关联的特定事件时，将运行事件触发器。支持的事件包括：ddl_command_start、ddl_command_end、table_rewrite和sql_drop。

  • ddl_command_start在运行CREATE、ALTER、DROP、SECURITY LABEL、COMMENT、GRANT、REVOKE或SELECT INTO命令之前发生。
  • ddl_command_end在命令完成之后和事务提交之前发生。
  • sql_drop仅针对 DROP 类型的 DDL 命令触发。在ddl_command_end前触发。

有关详细信息，请参阅 PostgreSQL 文档中的事件触发器触发矩阵。

例子

创建 DML 触发器。要在 PostgreSQL 中创建 Oracle DML 触发器的等效版本，请首先创建一个函数触发器，该触发器将存储触发器的运行逻辑。

CREATE OR REPLACE FUNCTION PROJECTS_SET_NULL()
  RETURNS TRIGGER
  AS $$
  BEGIN
  IF TG_OP = 'UPDATE' AND OLD.PROJECTNO != NEW.PROJECTNO OR
  TG_OP = 'DELETE' THEN
  UPDATE EMP
    SET PROJECTNO = NULL
    WHERE EMP.PROJECTNO = OLD.PROJECTNO;
    END IF;

    IF TG_OP = 'UPDATE' THEN RETURN NULL;
      ELSIF TG_OP = 'DELETE' THEN RETURN NULL;
      END IF;
  END;
$$ LANGUAGE PLPGSQL;

创建触发器。

CREATE TRIGGER TRG_PROJECTS_SET_NULL
  AFTER UPDATE OF PROJECTNO OR DELETE
  ON PROJECTS
  FOR EACH ROW
  EXECUTE PROCEDURE PROJECTS_SET_NULL();

通过从 PROJECTS 表中删除一行来测试触发器。

DELETE FROM PROJECTS WHERE PROJECTNO=123;
SELECT PROJECTNO FROM EMP WHERE PROJECTNO=123;

projectno
(0 rows)

创建一个 DDL 触发器，该触发器是 Oracle DDL 系统/模式级别触发器的等效版本（例如阻止对 HR 模式中的对象运行 DDL DROP 的触发器）。

创建事件触发器函数。

请注意，创建触发器函数时不带参数，并且返回类型必须为TRIGGER或EVENT_TRIGGER。

CREATE OR REPLACE FUNCTION ABORT_DROP_COMMAND()
RETURNS EVENT_TRIGGER
AS $$
BEGIN
  RAISE EXCEPTION 'The % Command is Disabled', tg_tag;
END;
$$ LANGUAGE PLPGSQL;

创建事件触发器，该触发器将在 DDL DROP 命令开始之前触发。

CREATE EVENT TRIGGER trg_abort_drop_command
  ON DDL_COMMAND_START
  WHEN TAG IN ('DROP TABLE', 'DROP VIEW',
    'DROP FUNCTION', 'DROP SEQUENCE',
    'DROP MATERIALIZED VIEW', 'DROP TYPE')
  EXECUTE PROCEDURE abort_drop_command();

通过尝试删除 EMPLOYEE 表来测试触发器。

DROP TABLE EMPLOYEES;

ERROR: The DROP TABLE Command is Disabled
CONTEXT: PL/pgSQL function abort_drop_command() line 3 at RAISE

总结

触发 Oracle PostgreSQL
在更新触发器之前，行级别 CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
/*Trigger body*/
END;
/		 CREATE TRIGGER check_update
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE myproc();
在更新触发器之前，语句级别 CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE ON projects
BEGIN
/*Trigger body*/
END;
/		 CREATE TRIGGER check_update
BEFORE UPDATE ON employees
FOR EACH STATEMENT
EXECUTE PROCEDURE myproc();
系统/事件触发器 CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/		 CREATE EVENT TRIGGER trg_drops
ON ddl_command_start
EXECUTE PROCEDURE trg_drops();
在触发器中引用:old和:new 在触发器主体中使用 “:NEW” 和 “:OLD”：
CREATE OR REPLACE TRIGGER Upper-NewDeleteOld
BEFORE INSERT OR UPDATE
OF first_name ON employees
FOR EACH ROW
BEGIN
:NEW.first_name := UPPER(:NEW.first_name);
:NEW.salary := :OLD.salary;
END;
/		 在触发器过程主体中使用 “NEW“ 和 ”OLD“：
CREATE OR REPLACE FUNCTION log_emp_name_upd()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.last_name <> OLD.last_name
THEN
INSERT INTO employee_audit (employee_ id,last_name,changed_on) VALUES (OLD.id,OLD.last_name,now());
END IF;
RETURN NEW;
END; $$

CREATE TRIGGER last_name_change_trg
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE log_last_emp_name_upd();
数据库事件级别触发器 CREATE TRIGGER register_shutdown
ON DATABASE SHUTDOWN
BEGIN
Insert into logging values
('DB was shut down', sysdate);
commit;
END;
/		 不适用
删除触发器 DROP TRIGGER last_name_change_trg; DROP TRIGGER last_name_change_trg on employees;
修改触发器运行的逻辑 可与创建或替换一起使用
CREATE OR REPLACE TRIGGER UpperNewDeleteOld
BEFORE INSERT OR UPDATE OF first_name ON employees
FOR EACH ROW
BEGIN
<<NEW CONTENT>>
END;
/		 在触发器中调用的函数上使用创建或替换（触发器保持不变）
CREATE or replace FUNCTION UpperNewDeleteOld()
RETURNS trigger
AS $UpperNewDeleteOld$
BEGIN
<<NEW CONTENT>>
END;
$UpperNewDeleteOld$
LANGUAGE plpgsql;
启用触发器 ALTER TRIGGER UpperNewDeleteOld ENABLE; alter table employees enable trigger Upper-NewDeleteOld;
禁用触发器 ALTER TRIGGER UpperNewDeleteOld DISABLE; alter table employees disable trigger Upper-NewDeleteOld;

有关详细信息，请参阅 PostgreSQL 文档中的 CREATE TRIGGER 和触发函数。

搜索

分类

标签