Convert triggers from PostgreSQL to MS SQL

Convert triggers from PostgreSQL to MS SQL

- in Featured
620
Comments Off on Convert triggers from PostgreSQL to MS SQL

This article exploresthe most frequent issues ofconverting PostgreSQL triggers into MS Server format. The primary reason of those issuesis missingparticularPostgreSQL 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

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 updateoperation completes. All modified records are collected in service tables “inserted” (for insert and update operations) or “deleted” (for delete operation).

PostgreSQL

Example below demonstrates this approach to converting PostgreSQL triggers into SQL Server format. The following trigger 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

Every trigger declared as “BEFORE DELETE” in PostgreSQLcan be converted into “AFTER DELETE” MS SQL trigger extracting all affected records from “deleted” service tablein 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 correspondingfragment of code.Assume, we have to migrate the thisdefinition from PostgreSQL to SQL Server:

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 intothe 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/time/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 howgreatest() 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.