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.
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)