CREATE TRIGGER Statement - Oracle to PostgreSQL Migration

In Oracle, you can use the CREATE TRIGGER statement to define the trigger action as a PL/SQL program block.

In PostgreSQL you have to use two statements CREATE FUNCTION … RETURNS TRIGGER and CREATE TRIGGER … EXECUTE FUNCTION.

Oracle:

  -- Data
  CREATE TABLE data1 (c1 CHAR(1));
 
  -- An audit table used in trigger
  CREATE TABLE data1_audit 
  ( 
     c1 CHAR(1), 
     operation VARCHAR2(30), 
     ts TIMESTAMP
  );
 
  CREATE OR REPLACE TRIGGER data1_audit_tr
     AFTER INSERT ON data1
     FOR EACH ROW
  BEGIN
     INSERT INTO data1_audit VALUES (:NEW.c1, 'inserted', SYSTIMESTAMP);
  END;
  /

Now when you insert a record to data1 table, you can notice that a record is also added to data1_audit table:

Oracle:

  INSERT INTO data1 VALUES ('a');  
  SELECT * FROM data1_audit;
 
  #    a       inserted       07-DEC-21 10.51.48.125000 PM

In PostgreSQL you have to function that returns TRIGGER first:

PostgreSQL:

  CREATE OR REPLACE FUNCTION data1_audit_tr_func() RETURNS TRIGGER
   AS $$
  BEGIN
    INSERT INTO data1_audit VALUES (NEW.c1, 'inserted', CURRENT_TIMESTAMP);
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

Then create a trigger that invokes this function:

PostgreSQL:

  CREATE TRIGGER data1_audit_tr
    AFTER INSERT ON data1
    FOR EACH ROW EXECUTE FUNCTION data1_audit_tr_func();

Now when you insert a record to data1 table, you can see that a record is also added to data1_audit table:

PostgreSQL:

  INSERT INTO data1 VALUES ('a');  
  SELECT * FROM data1_audit;
 
  #  c1 | operation |             ts
  # ----+-----------+----------------------------
  #  a   | inserted  | 2021-12-07 23:09:51.053237

For more information, see Oracle to PostgreSQL Migration.