Convert triggers from PostgreSQL to MS SQL

This article explores the most frequent issues of converting PostgreSQL triggers into MS Server format. The primary reason of those issues is missing particular PostgreSQL features in the target DBMS:

  • BEFORE INSERT/UPDATE/DELETE types of triggers are not supported in SQL Server
  • FOR EACH ROW pattern is not supported by SQL Server
  • SQL Server does not support functions that returns trigger

Image result for PostgreSQL

The task is to implement missing capabilities by other means of MS SQL. For example, semantic of BEFORE-triggers is to update record affected by the last operation before inserting/updating it into the database. “FOR EACH ROW” pattern allows to apply the trigger to all rows affected by the last insert, update or delete operation. SQL Server triggers can process affected records in the database after insert or update operation completes. All modified records are collected in service tables “inserted” (for insert/update operation) or “deleted” (for delete operation).

Let us demonstrate this approach on converting the following PostgreSQL trigger that updates Change_Time and Create_Time columns with the current date and time (in this example column “ID” contains unique values):

CREATE TRIGGER trigger1 BEFORE INSERT ON “Table1” FOR EACH ROW

BEGIN

     SET NEW.”Change_Time” = CURRENT_TIMESTAMP;

     SET NEW.”Create_Time” = CURRENT_TIMESTAMP;

END;

In SQL Server trigger for the same purpose can be composed as follows:

CREATE TRIGGER trigger1 ON [Table1]

AFTER INSERT AS

BEGIN

UPDATE [Table1] SET

           [Change_Time] = GETDATE(),

           [Create_Time] = GETDATE()

WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.[ID] = [Table1].[ID]);

END;

GO

PostgreSQL “BEFORE DELETE” triggers can be converted into “AFTER DELETE” MS SQL triggers by extracting all affected records from “deleted” service table in the similar way.

In SQL Server functions cannot return triggers, so each call of such functions from CREATE TRIGGER statement must be replaced by the corresponding fragment of code. Assume, we have to migrate the this definition from PostgreSQL to SQL Server:

 Image result for MS SQL

CREATE FUNCTION tbl_trigger() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

     IF (TG_OP = ‘INSERT’) THEN

           NEW.”Change_Time” = CURRENT_TIMESTAMP;

           NEW.”Create_Time” = CURRENT_TIMESTAMP;

           RETURN NEW;

     ELSIF (TG_OP = ‘UPDATE’) THEN

           NEW.”Change_Time” = CURRENT_TIMESTAMP;

           RETURN NEW;

     END IF;

     RETURN NULL;

END;

$$;

CREATE TRIGGER tbl_before_trigger BEFORE INSERT ON “Table1” FOR EACH ROW EXECUTE PROCEDURE tbl_trigger();

It may be converted into the following two triggers in MS SQL:

CREATE TRIGGER tbl_before_insert ON [Table1]

AFTER INSERT AS

BEGIN

UPDATE [Table1] SET

           [Change_Time] = GETDATE(),

           [Create_Time] = GETDATE()

WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.[ID] = [MyTable].[ID]);

END;

CREATE TRIGGER tbl_before_update ON [Table1]

AFTER UPDATE AS

BEGIN

UPDATE [Table1] SET

           [Change_Time] = GETDATE()

WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.[ID] = [Table1].[ID]);

END;

GO

PostgreSQL and SQL Server have similar sets of functions and operators, however these is difference as well. Therefore, every specific PostgreSQL function must be converted into MS SQL equivalent according to this table:

PostgreSQL SQL Server
coalesce() isnull()
current_date, current_time, current_timestamp getdate()
date_part() datepart()
extract() datepart()
greatest() No direct equivalent, see custom code below
least() No direct equivalent, see custom code below
now() getdate()
position() charindex()
expression::type cast(expression as type)
string1 || string2 string1 + string2

 

Here is how greatest() and least() functions may be emulated in MS SQL:

SELECT Greatest=MAX(col), Least=MIN(col)

FROM table_name

CROSS APPLY (

    SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3

    UNION ALL SELECT col4) a(col)

GROUP BY primary_key

There are more articles about conversion between PostgreSQL, SQL Server and other popular DBMS available at https://www.convert-in.com