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:
FOR EACH ROWpattern in triggers
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
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:
|current_date, current_time, current_timestamp||getdate()|
|greatest()||must be replaced by custom code (see below)|
|least()||must be replaced by custom code (see below)|
|newid()||uuid_in(md5(random()::text || now()::text)::cstring)|
|expression::type||cast(expression as type)|
|string1 || string2||string1 + string2|
Custom SQL-query for emulating
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
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
IF (@@ROWCOUNT = 0) RETURN;
More articles about PostgreSQL, SQL Server and other popular DBMS are available at http://www.convert-in.com/articles.htm