Home > Documentation > MS SQL to PostgreSQL

Converting triggers is a part of database migration process that can hardly be automated. This whitepaper explores the most important steps of converting trigger from SQL Server to PostgreSQL.

1. Trigger Procedure. In SQL Server the main code of trigger follows right after CREATE TRIGGER declaration. PostgreSQL requires trigger's code as separate procedure that is referenced from CREATE TRIGGER statement.

2. Access to Data. MS SQL provides special tables "inserted" and "deleted" containing information about inserted/updated(new value) and deleted/updated(old value) rows. Trigger may iterate those tables to take an action for all rows being inserted/updated/deleted. PostgreSQL does not have such tables. Instead, it provides pattern FOR EACH ROW for the same purpose. Data from inserted or updated (new value) row can be accessed in PostgreSQL as NEW.column_name. Data from deleted or updated (old value) row can be accessed as OLD.column_name.

Let us illustrate the steps mentioned above on example. Assume, there is a trigger in SQL Server database declared as follows:

CREATE TRIGGER [dbo].[UpdateAssignedTranscriptionistID]
   ON  dbo.Reports AFTER INSERT, UPDATE
AS
BEGIN
        UPDATE dbo.Study
        SET Study.AssignedTranscriptionist = INSERTED.CreatedBy
        FROM
                dbo.Study
                INNER JOIN INSERTED ON Study.StudyUID = INSERTED.StudyUID
        WHERE Study.ReportCount = 0
END

In PostgreSQL the same trigger should look like:

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

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

Click here for more articles about SQL Server, PostgreSQL and other popular database management systems.