postgresql - 将 DB2 触发器重写为 PostgreSQL 触发器

Question

我正在尝试将以下触发器从 DB2 迁移到 PostgreSQL:

DB2 触发器:

CREATE TRIGGER trig1
AFTER UPDATE ON table1
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.field1 <> O.field1)
BEGIN ATOMIC
INSERT INTO table1(field1, field2)
VALUES (N.field1, O.field2);
END #

我将触发器重写为PostgreSQL标准(不确定是否正确):

PostgreSQL 触发器:

CREATE OR REPLACE FUNCTION create_table1_history() RETURNS TRIGGER AS $table1_history$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            INSERT INTO table1 
                SELECT 
                NEW.field1, OLD.field2;
            RETURN OLD;
        END IF;
        RETURN NULL;
    END;
$table1_history$ LANGUAGE plpgsql;


CREATE TRIGGER trig1
AFTER UPDATE ON table1
    FOR EACH ROW EXECUTE PROCEDURE create_table1_history();

现在我不确定如何在我的 PostgreSQL 触发器中添加以下条件？

WHEN (N.field1 <> O.field1)

Answer 1

您可以添加触发触发器的条件:

CREATE TRIGGER trig1
AFTER UPDATE ON table1
    FOR EACH ROW 
    WHEN (new.field1 <> old.field1)
    EXECUTE PROCEDURE create_table1_history();

如果列可以包含 NULL 值，使用它可能会更好

    WHEN (new.field1 IS DISTINCT FROM old.field1)

---

触发器功能可以简化，并且您不需要检查 TG_OP，因为触发器本身被定义为仅在更新时触发

CREATE OR REPLACE FUNCTION create_table1_history() 
  RETURNS TRIGGER AS 
$table1_history$
BEGIN
   INSERT INTO history_table
   values (NEW.field1, OLD.field2);
   RETURN OLD;
END;
$table1_history$ LANGUAGE plpgsql;