CODEWITHSUNDEEP
postgresqldb2database-trigger
user3193317
user3193317

Reputation: 141

Rewrite DB2 trigger as PostgreSQL Trigger

I'm trying to migrate the following trigger from DB2 to PostgreSQL:

DB2 Trigger:

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 #

I rewrote the trigger to PostgreSQL standard (not sure if it is correct):

PostgreSQL Trigger:

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();

Now I am not sure how to add the following condition in my PostgreSQL Trigger?

WHEN (N.field1 <> O.field1)

Upvotes: 0

Views: 266

Answers (2)

user330315
user330315

Reputation:

you can add a condition on when the trigger is fired:

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

If the column can contain NULL values, it might be better to use

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

The trigger function can be simplified as well as you don't need to check TG_OP as the trigger itself is defined to only fire on UPDATEs

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;

Upvotes: 2

user3193317
user3193317

Reputation: 141

Just add another if:

IF (NEW.field1 <> OLD.field1) THEN
     IF (TG_OP = 'UPDATE') THEN

Upvotes: 0

Related Questions