1

I have been tasked to migrate a database from PostgreSQL 9 to SQL Server 2008 R2, and I have found some issues I need to address. I have quite limited experiences with PostgreSQL I'm afraid.

  1. Most keys are BigInt. In PostgreSQL a function is called to get the next ID and this is done in advance in the code for whatever reason. Can this be done in the same manner in SQL Server or am I forced to create an key index table to keep track of the current ID. IDENTITY(1,1) will afaik deliver a new ID on the insert.

  2. There are some stored procedures that returns NEW as a TRIGGER. What would be the equivalent in the SQL Server language?

  3. What impact has the difference in triggers given that PostgreSQL triggers are row based?

Improve this question
2
  • 2
    There are statement-level triggers, too, in postgres. Just saying. BTW, if that generated ID is not used for anything else but adding the ID to the row, you can safely use an identity instead. Returning NEW in a row-level trigger means the new row will be written to the table.
    – András Váczi
    Commented Sep 4, 2015 at 9:56
  • 3
    PostgreSQL 9.? There are four major versions starting with 9.
    – András Váczi
    Commented Sep 4, 2015 at 10:24

1 Answer 1

Reset to default
3

  1. You can create your keys using a SEQUENCE. Every time you need a new ID from the sequence, you just have to issue SELECT NEXT VALUE FOR SomeSequence;. Unfortunately not all versions of SQL Server support sequences (it's 2012 and up), so if you're stuck with SQL Server 2008 R2 you will have to code it by yourself.

    See Sequence Tables by Paul White for an example.

  2. As far as I know, returning NEW from a BEFORE trigger means the INSERT or UPDATE operation that originally fired the trigger will take place. The trigger function may change the column values of it. For a description and examples check the documentation.

    You can achieve the same thing in SQL Server with regular DML statements such as UPDATEs and INSERTs, joining the destination table with the logical tables INSERTED and DELETED. You could also decide to implement the operation on behalf of the original statement, using an INSTEAD OF trigger.

  3. Triggers in Postgres are either row-based or statement-based. Translating FOR EACH ROW into joins to INSERTED/DELETED is just a matter of using the correct keys to correlate the tables.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.