Convert triggers from SQL Server to PostgreSQL
|[SQL Server to PostgreSQL Converter] [Download] [Buy]|
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
CREATE TRIGGER declaration. PostgreSQL requires
trigger's code as separate procedure that is referenced from
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
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.