Oracle triggers and PostgreSQL trigger procedure

With Amazon DMS, you can migrate databases to Amazon while replicating database code objects, such as triggers across source and target databases. Triggers are database objects that automatically run a defined procedure when an event occurs, such as inserting, updating, or deleting data in a table. Oracle triggers and PostgreSQL trigger procedures define the logic and actions to be performed when specific events occur in the database.

Oracle usage

A trigger is a procedure that is stored in the database and fired when a specified event occurs. The associated event causing a trigger to run can either be tied to a specific database table, database view, database schema, or the database itself.

Triggers can be run after:

Trigger types

Examples

Create a trigger that runs after a row is deleted from the PROJECTS table, or if the primary key of a project is updated.

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

Create a SYSTEM/Schema trigger on a table. The trigger fires if a DDL DROP command runs for an object in the HR schema. It prevents dropping the object and raises an application error.

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

For more information, see CREATE TRIGGER Statement in the Oracle documentation.

PostgreSQL usage

A trigger is a procedure that is stored in the database and fired when a specified event occurs. DML triggers in PostgreSQL share much of the functionality that exists in Oracle triggers.

Unlike Oracle triggers, PostgreSQL triggers must call a function and don’t support anonymous blocks of PL/pgSQL code as part of the trigger body. The user-supplied function is declared with no arguments and has a return type of trigger.

PostgreSQL CREATE TRIGGER synopsis

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

There are some cases that can fire multiple triggers numerous times. This includes triggers that aren’t planned to run, such as:

PostgreSQL DML triggers

PostgreSQL triggers can run BEFORE or AFTER a DML operation.

PostgreSQL triggers can run INSTEAD OF a DML command when created on views.

PostgreSQL triggers can run FOR EACH ROW affected by the DML statement or FOR EACH STATEMENT running only once as part of a DML statement.

PostgreSQL event triggers

An event trigger runs when a specific event that is associated with the trigger occurs in the database. Supported events include: ddl_command_start, ddl_command_end, table_rewrite and sql_drop.

For more information, see Event Trigger Firing Matrix in the PostgreSQL documentation.

Examples

Create a DML trigger. To create an equivalent version of the Oracle DML trigger in PostgreSQL, first create a function trigger which will store the run logic for the trigger.

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 the trigger.

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

CREATE TRIGGER

Test the trigger by deleting a row from the PROJECTS table.

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

projectno
(0 rows)

Create a DDL trigger that is an equivalent version of the Oracle DDL System/Schema level triggers (such as a trigger that prevent running a DDL DROP on objects in the HR schema).

Create an event trigger function.

Note that trigger functions are created with no arguments and must have a return type of TRIGGER or 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;

CREATE FUNCTION

Create the event trigger, which will fire before the start of a DDL DROP command.

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

Test the trigger by attempting to drop the EMPLOYEES table.

DROP TABLE EMPLOYEES;

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

Summary

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

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;
/

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;

For more information, see CREATE TRIGGER and Trigger Functions in the PostgreSQL documentation.