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
CREATE TRIGGER declaration. In PostgreSQL
trigger's code must be arranged in separate procedure that is referenced
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
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();