Home > Documentation > Oracle to PostgreSQL

Convert triggers from Oracle to PostgreSQL

Triggers are quite hard to migrate due to complicated nature of these database entries. This whitepaper discovers main steps of triggers migration from Oracle to PostgreSQL.

1. Trigger Procedure. The source code of Oracle trigger follows right after CREATE TRIGGER declaration. In PostgreSQL trigger's code must be arranged in separate procedure that is referenced from CREATE TRIGGER statement.

2. Access to Data. Data from inserted or updated (new value) row can be accessed in Oracle as :NEW.column_name. Data from deleted or updated (old value) row can be accessed as :OLD.column_name. PostgreSQL provides NEW.column_name and OLD.column_name expressions for the same purpose

The example below demonstrates conversion steps mentioned above. Assume, there is Oracle trigger defined as:

CREATE TRIGGER UpdateTranscriptionistID 
AFTER INSERT OR UPDATE ON Reports 
FOR EACH ROW 
BEGIN
        UPDATE Study
        SET AssignedTranscriptionist = :NEW.CreatedBy
        WHERE StudyUID = :NEW.StudyUID AND ReportCount = 0
END;

In PostgreSQL the same trigger must be created as follows:

CREATE FUNCTION UpdateTranscriptionistID_proc() RETURNS trigger AS $$
BEGIN
	UPDATE Study
	SET AssignedTranscriptionist = NEW.CreatedBy
	WHERE StudyUID = NEW.StudyUID AND ReportCount = 0;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER UpdateAssignedTranscriptionistID
	AFTER INSERT OR UPDATE ON Reports
	FOR EACH ROW EXECUTE PROCEDURE UpdateAssignedTranscriptionistID_proc();