Oracle to SQL SERVER Migration – Triggers Used While Migration

I am a big supporter of working on something that I get used. This is a trend I see that most of us are used to. When was the last time you switched loyalty from one phone to another? I know most of us have multiple devices that we use, but rarely that I have seen switch from an Android to iPhone to Windows to other devices. These are second to human nature. In the past 3+ years, I have been a big-time user from using Android phones. The model that I pick up depends on the price, hardware specification, specific features of my like and more. Let us learn about Oracle to SQL migration.

When it comes to enterprise software, switching loyalty is based on several factors. These days in my consulting engagements, I do a lot of performance tuning exercises for clients. These are super-fast engagements and have a lot of quick fixes happens to client’s environments. In a recent tuning exercise, I found a lot of problems in deployment which was using triggers. I was not a big fan of triggers and this will for a conversation on some other day. Having said that, I got to the bottom of it to understand that this database was migrated from Oracle. After tuning the system, I had to explain how the differences are between Oracle and SQL Server.

This blog is more of a recap of the conversation I had with the team. In a simplistic manner, below is a table that summarizes my understanding.

Oracle to SQL SERVER Migration - Triggers Used While Migration trigger-oracle-sql-01-800x432

As I wrap up, wanted to make a comment around, SQL Server does not have an exact equivalent of Oracle’s Before trigger. During migrations Oracle’s ”Before” Trigger are replaced by SQL Server’s “InsteadOf” trigger.

Also, note that many Triggers in Oracle databases only assign default values – this is not efficient and overcomplicates the implementation. For default values, use SQL Servers built in database default value capability. Don’t use (or continue to use) Triggers to assign default values.

Are you still using triggers with your SQL Server? What are the scenario’s you still use them? Please let me know via comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER 2016 – Trace Flag 1117 is Discontinued. Use the Options Provided with ALTER DATABASE
Next Post
SQL SERVER – Why Does Encrypted Column Show NULL on Subscriber?

Related Posts

1 Comment. Leave new

  • Nice article Pinal.
    Triggers need special attention during migration from Oracle to SQL Server as they are not easy to work with. In your second last paragraph you have mentioned “Triggers in Oracle databases only assign default values” Can you please explain this with example so we will know the problem that you encountered during the migration and how you handled it.

    Thank You,

    Reply

Leave a Reply

Menu