Converting PostgreSQL triggers into MS SQL format

This article covers main issues of converting triggers from PostgreSQL to SQL Server. Basic knowledge in SQL programming are required to understand the contents.

The main bottleneck of the conversion process is lack of certain features in the target DBMS:

• SQL Server does not support BEFORE INSERT/UPDATE/DELETE triggers
• SQL Server does not allow using FOR EACH ROW pattern in triggers
• SQL Server does not support functions that returns trigger

Let us start with first two issues. The logic of BEFORE-triggers is to update all affected records before inserting/updating it into the database. The logic of FOR EACH ROW pattern is to apply the trigger action to all rows affected by insert/update/delete operation. The same can be done in SQL Server after records are inserted/updated in the database. All affected records appear in service tables "inserted" (for insert/update operation) or "deleted" (for delete operation).

Assume, there is trigger that updates LastChanged and CreatedOn date columns with the current date and time. It is defined in PostgreSQL as follows:

CREATE TRIGGER before_insert_trigger BEFORE INSERT ON "MyTable" FOR EACH ROW  
BEGIN
	SET NEW."LastChanged" = CURRENT_TIMESTAMP;
	SET NEW."CreatedOn" = CURRENT_TIMESTAMP;	
END;

In SQL Server the same trigger can be created like this:

CREATE TRIGGER before_insert_trigger ON [MyTable] 
AFTER INSERT AS 
BEGIN
UPDATE [MyTable] SET 
		[LastChanged] = GETDATE(), 
		[CreatedOn] = GETDATE() 
WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.[ID] = [MyTable].[ID]);
END;
GO

Following the same approach, all BEFORE DELETE PostgreSQL triggers can be converted into AFTER DELETE MS SQL triggers extracting all affected records from "deleted" service table.

Since SQL Server functions cannot return triggers, it is necessary to replace each call of such functions from CREATE TRIGGER statement by the appropriate block of code. For example, the following PostgreSQL definition:

CREATE FUNCTION mytable_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF (TG_OP = 'INSERT') THEN
		NEW."LastChanged" = CURRENT_TIMESTAMP;
		NEW."CreatedOn" = CURRENT_TIMESTAMP;
		RETURN NEW;
	ELSIF (TG_OP = 'UPDATE') THEN
		NEW."LastChanged" = CURRENT_TIMESTAMP;	
		RETURN NEW;
	END IF;
	RETURN NULL;
END; 	
$$;

CREATE TRIGGER mytable_before_trigger BEFORE INSERT ON "MyTable" FOR EACH ROW EXECUTE PROCEDURE mytable_trigger();

must be replaced by these triggers in SQL Server:

CREATE TRIGGER before_insert_trigger ON [MyTable] 
AFTER INSERT AS 
BEGIN
UPDATE [MyTable] SET 
		[LastChanged] = GETDATE(), 
		[CreatedOn] = GETDATE() 
WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.[ID] = [MyTable].[ID]);
END;

CREATE TRIGGER before_update_trigger ON [MyTable] 
AFTER UPDATE AS 
BEGIN
UPDATE [MyTable] SET 
		[LastChanged] = GETDATE() 
WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.[ID] = [MyTable].[ID]);
END;
GO

Although most of functions and operators in PostgreSQL and SQL Server has the same names and argument lists, every specific PostgreSQL function must be replaced with MS SQL equivalent:

Custom SQL-query for emulating greatest() and least() functions:

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

And the last but not least - do not forget to optimize the resulting SQL Server trigger to make sure its execution time is as short as possible. Obviously, if the statement calling trigger does not affect any rows, the trigger should return immediately. There is system variable @@ROWCOUNT that indicates how many records have been changed by previous statement. So, it is quite reasonable to place the following code at the top of each trigger:

IF (@@ROWCOUNT = 0) RETURN;

More articles about PostgreSQL, SQL Server and other popular DBMS are available here