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 database management system:
- BEFORE INSERT/UPDATE/DELETE types of triggers are not supported in SQL Server
- FOR EACH ROW pattern is not supported by SQL Server
- Functions in SQL Server cannot return trigger objects
The main challenge of triggers migration from PostgreSQL to SQL Server is to implement features missing in the target DBMS. For example, semantic of BEFORE-triggers is to update record affected by the last operation before inserting/updating it into the database. Pattern “FOR EACH ROW” applies action of the trigger to all rows affected by the last insert, update or delete operation. Triggers in SQL Server database can process affected records after insert or update operation completes using special service tables. All inserted/updated records are stored in service tables “inserted” and deleted records are stored in service tables “deleted”.
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:
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 the official site of Intelligent Converters.
