Be Very Careful When You Write Sql Trigger

In many scenarios we write triggers on database tables. Before writing a trigger we need to understand what trigger is and how exactly it works. Because, lack of clear knowledge on triggers can ake your life difficult. Trigger is actually a procedure that runs in response of an event fired due to performing some operations on database tables. The events could be insert, update or delete. Now, the question is how database handles the execution of a trigger when it fires?

If you write a trigger for insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in the memory. Then it performs the insert operation and after that the statements inside the trigger executes. We can query the “INSERTED” table to manipulate or use the inserted row/s from the trigger.

Similarly if you write a trigger for delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row.

More importantly you must understand how an update trigger works. After firing an update trigger it works in the following sequence:

i. All constraints are enforced.
ii. All declarative referential integrity (DRI) constraints are enforced (via foreign keys).
iii. The inserted and deleted tables are created in memory for use within the trigger.
iv. The triggering action (in this case the UPDATE statement) is executed.
v. The AFTER UPDATE trigger executes.

From the above steps you can see that, no table called “UPDATED” is created. Actually on database no operation called update executes. Internally it actually delete the rows to be updated and keep the deleted rows in DELETED table. The updated rows that are sent to update the table are kept in INSERTED TABLE. After the old rows are backed up to the DELETED table, updated rows from INSERTED tables get inserted into the targeted table.

So, from an update trigger we can access both INSERTED and DELETED table though directly we may not execute any insert or delete operation. This is a very important concept for us. Here I am providing the mistakes you may make if you are not clear on this.

Consider the following trigger:

CREATE TRIGGER TriggerName ON YourTableName

AFTER UPDATE ,INSERT , DELETE
AS
if(exists(select Contact_Id from inserted where Contact_id is not null))
begin
--Do your operation
end
if(exists(select Contact_Id from deleted where Contact_id is not null))
begin
--Do your operation
End

Here the developer wrote the trigger for all the events on the table and expecting to do some operation if Contact_ID is inserted, deleted or updated in to the table.

Now note carefully the mistakes that the developer did in this trigger. For example an operation is executed on the table which updates some other field other than Contact_ID. Now if Contact_ID is a not null column of the table we will never get null from INSERTED and the DELETED table. So, here even though Contact_ID is not updated the operation of the triggers will execute.

It is not finished yet. It has more problems. The developer wrote the 2nd if condition assuming that DELETED table will be created only when trigger fires for any Delete operation on the table. But you see, as a matter of fact this table is also available when the trigger fires for update operation.

The situation will be the worst if the developer thinks the first if statement will be successful for INSERT and UPDATE operation and 2nd if statement will be successful for DELETE operation. Note that, in reality both 1st and 2nd if statement will be successful for update operations! So, if the developers want some code to execute for all the operations he might do the mistake of writing the same code in both 1st and 2nd if block. This will in turn execute the same operation twice while any update operation is done. Doesn’t it sound very silly and surprising? Yes. But if you are not careful these mistakes can happen anytime and can take you to hell from the heaven you are currently in.

Another Mistake:

Say you are updating 5 rows of a table with a single update statement. Then, in trigger you are querying as follows:

DECLARE @IsPublished [bit] 

SET @IsPublished = (SELECT [IsPublished] FROM [inserted])

Here, you are expecting 1 row in the INSERTED table all the time. You may think internally sql server creates separated INSERTED AND DELETED table for each of the 5 rows. But No! In reality it will create 1 INSERTED table and 1 DELETED table. Each of the tables will contain all the 5 rows. So, the above sql statement will return an error. You should always be careful of this.

So, while writing a trigger keep an eye on the following points:

1. If you write a single trigger for multiple event, be very careful to ensure that your trigger does not execute for unwanted events.
2. When writing update trigger always check if your desired column is updated by using IF UPDATE(ColumnName).
3. Be very careful in querying INSERTED and DELETED table.
4. Try to avoid cursor from the trigger.
5. Ensure that your trigger is not creating any deadlock/Infinite loop on your database.

Wish you write error free triggers and save hours of trouble shooting!   

SOLIDify your software design concepts through SOLID

Starting With Real World

If you want to create a dolls house you may easily do that by your own. It does not necessarily need any upfront design. You just gather some materials (say papers) and start building from your imagination.

Give the doll’s house to your daughter. She will play with this and after some time she will not like it anymore. Then you throw it away and build another one.

Now your wife is asking you to build a house on the land you have just purchased. Is it possible for you to build the house by your own? I know this is an invalid question. Well, is it possible for you to start building the house if you are provided 100 people with you? The answer is no. If I ask why? You will instantly give the obvious answer – “Building a house involves lot of concerns and you need experts to handle those concerns before starting the real construction work.”

Some concerns could be

1. Your wife’s dream about the house.

2. The maximum area of land availability.

3. Your budget.

4. Core architectural design.

5. Floor planning.

6. Utility planning.

7. Car parking.

8. And many more.

So, the first point you need to start the work is to talk with your wife generously so that you know what exactly she wants at her house. Then go to the construction company who will work as a contractor to build your house. Tell them your expectation, area and budget. You will then be sent to the civil engineer and the architect. They will know your requirements in detail and will do thorough analysis on that. Later the civil engineer and the architect will come out with a design and plan of construction. They will consider all the concerns and are expected to handle those with maximum efficiency. The best they do it, the best your house will become. On the other hand if the architect and civil engineer do poor work, you house will suffer in the long run.

So, it is not you and the workers who are responsible for a great house to construct but it is the architect and civil engineer who can make it happen of-course backed by skilled workers who are capable of working according to plan. If your design and architecture is great, the hardest part is done.

I am sure as you are reading this article, you are a software engineer. Can you relate this house construction paradigm with the software development world?

Let us construct your house hypothetically as if it is a software and try to relate the design strategies of this house construction with some software design principles so that we can realize the necessity of following standard principles while design our software.

First of all who is the client of this house? It is primarily your wife who provided you the specification of her expectations from the house. Then you are also the client who will be the point of contract from client side. The construction company is the software house who is going to work to build your house. So, an agile environment is setup that can ensure the best production as both parties will be in close touch all the time. Now what?

Now, the architecture and civil engineer will start analyzing your specification to come up with a good design. In reality building a house is a very big task. So, we will not touch too many points to make it simple. The goal will be, the designer should design your building in the best way and we will see it through some example building construction concerns.

Base foundation of the building:

The civil engineer will design the base foundation of the house. He perhaps will consider the following while doing the design:

1. Maximum area of land.

2. Maximum number of floors can be accommodated.

3. How many floors you will want to construct initially.

4. Your plan of extension in future.

Then perhaps,

5. How deep the foundation will be.

6. How many piers will be needed and how they will be distributed.

7. Etc.

During designing the base foundation plan, the civil engineer will need to work closely with the architect so that the distribution of piers does not impact the interior of the building.

Now, once the foundation is done, it is closed. The piers will be extendable and on top of that your house will be extended floor by floor. Let’s say you will construct your house initially with 2 floors. After some years the world under go in recession and you lost your job. So, you want to construct 2 more floors on top of your house using your saved money so that you can give that in rent and earn some continuous money. Let’s say, the engineer had not considered that you may want to construct more floors on your house. So, he designed the foundation just for 2 floors and at the top the piers are sealed. So what will you do now? Unfortunately you have no option but demolishing your house, re-do the foundation and construct it again. So, the engineer could have killed you. Is not it?

On the other hand, if the engineer is good, he will do the foundation so that you can build more floors on top of it and the piers on the top floors are extendable. So, your base foundation is closed for any modification but still open for extension. Here we have learned a very important principle that is also applicable in software paradigm. That is, you must build your base classes of the software with enough knowledge of the module you are developing and then expose the extension points from the base class so that it can be extended by the child classes.

In one sentence we can tell “Your base classes will be closed for any modification and still open for extension.” This principle is formally known as Open-Close Principle in software world.

More example of Open-Close Principle:

See the example codes below. If you understand the Open Close Principle you will easily see how the 1^st example violating the Open Close Principle and how the second example honoring it.

Image – Violation of Open Close Principle

Image – Honoring Open Close Principle

Interior Architecture – Where to fit Air Conditioners:

As I have already said, there are so many concerns we will have to handle to construct a house. Among all those concerns very small concern like where to install your air conditioner in your bed room is no less important. Say, your architect designed the room so that you have a space for fitting air conditioner inside the window. During his design he thought of a window air conditioner and kept a place for that in the window. Now, the time changes, and you may like to install a bigger air conditioner or you may like to install a split ac replacing your old window one. What will you need to do?

One option is you change your window so that it allows you a bigger space for the ac. But you see if you do this, the size of the window may decrease which eventually may not be good for day light transition and air flow of your room.

So, ideal solution could be if the space for the air conditioner were not inside your window, rather into the wall, you could alter the space for your new ac in the wall engaging perhaps with very low skilled labor.

See the benefits here:

1. You do not need to change your window.

2. Your room will not be at risk of low ventilation and lighting.

Do you see the problem in the design where ac is to fit into the window? We all know that purpose of a window is to provide proper light and ventilation to your room. So, window should have been changed only if you need to adjust the lighting and ventilation. It was not good if you had to change your window because you needed to install a new ac.

This flaw in your build design has similarity with a very important software design principle. The principle says your objects should change for one and only one reason. In other words, your objects should always serve one and only one responsibility. Formally this principle is known as Single Responsibility Principle in software world.

More example of Single Responsibility Principle:

See the simple example below. Here 2 task is done by ApplicationSecurityContext class. One is authentication and the other is Login. You know in modern software world there could be many ways of authenticating users and also many ways to make the user logged to your application. Authentication can be done through database, windows authentication, single sign-on etc and login can be done through forms authentication, windows authentication, LDAP based authentication etc. So, it is natural that your application’s login and authentication mechanism can change. But if you implement both in the same class this class gets 2 very big concern of your application to manage. Here authentication is more of central concern of the system and login is closely related to the client interface of the application (Desktop app, Smart client app, Web app etc). In this situation we must not implement this 2 concern in same class and if we do it will clearly violate the single responsibility principle. And if we continue to mix up these type of concerns in a single class, it will become a BBOM gradually.

Image: Violating Single Responsibility Principle

What is the solution then?

Image: Honoring Single Responsibility Principle

We can create a separate class for authentication. The best approach will be to have an interface like IAuthenticationProvider and implement your desired authentication implementing the interface. The FormsAuthenticationProvider class implements the forms authentication for your application. Later you can easily implement other mechanism of authentication if required. So, it is honoring both Single Responsibility Principle and Open Close Principle.

Designing the car parking area:

In Modern buildings car parking is one of the very important areas of concern. Your engineer and architect needs to design the piers of your building so that your basement or ground floor can accommodate as many cars as possible. While doing the design, they should consider the type of cars they should support. Normally in residential houses the zips could be the largest vehicle to support. So, your engineer should design the car parking so that each space supports light weight vehicles. Now, if your architect and engineers are not ver careful they may design the space only for sedan cars. The consequence of this, when you will need to accommodate your new prado in your car parking you cannot do that.

So, the design principle should be, each parking space should support one light weight vehicle. In that way it should also support any type of light weight vehicle whether it is a small car or a big prado jip.

This car parking concern also can describe another very important software design principle. The principle says, in a system of an object X is used and if object Y is subtype of object X then, in that system object X should be replaceable by object Y. More formally this principle is called Liskov's substitution principle in software world.

More Example of Liskov's Substitution Principle:

Liskov substitution principle basically says the inheritances that you create in your application must be correct. Here is the classic example for which the Liskov's Substitution Principle is violated. In the example 2 classes are used: Rectangle and Square. Let's assume that the Rectangle object is used somewhere in the application. We extend the application and add the Square class. The Square class is returned by a factory pattern, based on some conditions and we don't know the exact what type of object will be returned. But we know it's a rectangle. We get the Rectangle object, set the width to 5 and height to 10 and get the area. For a rectangle with width 5 and height 10 the area should be 50. Instead the result will be 100. (This example is taken from http://www.oodesign.com/liskov-s-substitution-principle.html )

Image – Violating Liskov Substitution Principle

Electricity Distribution Lines:

Among many of the utilities that you must have in your building electricity is perhaps one of the most important one. Your engineer and architect will perhaps need to work with an electrical engineer to design the electricity distribution line of your building. One approach for this could be, the whole supply of current in your building will come from a single root. But you see this will make the whole supply chain too complicated. It will make it very difficult to manage, extend and trouble shoot. So, what should be the solution?

In fact the electricity line should be interfaced in multiple segments. First of all each floor may have a separate root and then perhaps the best way would be to have each room having separate root. If you follow this design principle, it will be very easy for you to manage and trouble shoot your electric supply lines.

Again, this has similarity with yet another important software design principle. This principle says once an interface has become too 'fat' it needs to be split into smaller and more specific interfaces so that any clients of the interface will only know about the methods that pertain to them. This principle is known as Interface Segregation Principle in software world.

More example of Interface Segregation Principle:

Interface segregation principle is closely related with single responsibility principle I explained earlier in this write-up. Here creating a different interface for IAuthenticationProvider segregating the authentication concern from the login mechanism. In the same way whenever we see a single interface is becoming too heavy and entertaining more than one concern we should segregate the interface and create interfaces for each individual task to perform. Please see the Single Responsibility Principle code block for this example.

Overall building development process:

So far, we have talked about few of the concerns of your building construction. Now let us talk about the process of the building construct. You have engaged the civil engineer and the architect and they have completed the design. Now, the actual construction will start. How will the massive construction work will go through? Definitely there be a team leader. The team leader will eventually have different teams. For example construction workers, electricians, purchase team and so on. Now, to each team the responsibility will be distributed and each team will have a team leader itself. So, the team leader of the whole construction work will just know the individual team leaders of each team. He does not need to know who the team members are working in each team. In this way, the dependencies among different teams are abstracted to individual team leaders. So, any change in the requirement and design will not need to be transmitted to all of the workers working in the project. The job of the works is just to implement what their team leaders ask to do.

So, the principle team leader does not depend on different teams through the team members rather he depends on different teams through the individual team leaders. And individual workers depend on their team leader to know what they need to implement.

This process of building construction can demonstrate another greatly important software design principle. The principle says:

1. High-level modules should not depend on low-level modules. Both should depend on abstractions.

2. Abstractions should not depend upon details. Details should depend upon abstractions.

--Wikipedia

The principle is known as Dependency Inversion Principle.

More example of Dependency inversion principle:

We actually already have seen code example of Dependency Inversion Principle. Again you if look at the code block for single responsibility principle you will see a parameter of type IAutheticationProvider is passed to the method LogIn. Here the client of this LogIn method does not need to know the actual implementation of IAutheticationProvider directly rather it gets the authentication through the abstract layer of IAutheticationProvider interface.

Image - Dependency Inversion Principle
In modern software world there are quite a few dependency injection container that facilitates us to program honoring dependency inversion principle. Some of those are Unity, NInject, Windsor etc. I have plan to have a separate write up on this later. So, keep following 

So far in this write-up I have tried to explain 5 very important design principles that we should always honor while designing and programming our softwares. Collectively these principles are called SOLID.

Image - SOLID copied from wikipedia

Beside these there are some other principles that we should have knowledge about. But again I have plan to write on those later. So, keep watching J.
Thanks for your patience to read this write-up. I would appreciate your comments and feedbacks. Discussions always can boost our concepts and decision making capability for software design.

Two very common design patterns to show how to think through simple software design challanges.

Design Pattern is perhaps the mostly used buzz word for software professionals. The geeks of the industry always talk about it and the novices got tempted to learn design patterns reading the theory. There are lot of books and articles on different design patterns. In many interviews you may learn questions like do you know design pattern? Do you know XXX design pattern? Thus perhaps many software developers do have a look on design pattern tutorials before any interview to face. But really, you cannot learn design pattern in this way. As a matter of fact design pattern is not something to learn.

Design Pattern is not something to learn! Yes. Design patterns are nothing but some great ways to solve some classic software design scenarios. If you know the principles of software design and give effort to solve the design challenges in your projects, you will find, eventually you have used one or more design patterns even without knowing about that. And this is the way all the design patterns evolved. If a great software designer solves a design challenge there is every possibility, a design pattern will be used or may be a new design pattern will be created!

To me design patterns are some guidelines that all software developers should know so that when he works in any project he can solve the design challenges easily. So, it is more to be habituated and practiced than to be learned. When you read about any design pattern you should try to grasp what are the problems that design pattern is trying to solve and what was the thinking process to come up with that design pattern. And when you work in your project, before jump to code you need to find what are the design challanges exist in your work. Then, you should solve those design challenges and then start coding. During thinking about your design challenges if you remember any similar challenge that you found while reading about any design pattern go back to that, have a look on that and if it really solves your problem use that. Or perhaps use that in a way that suits in your scenario. The more you practice this procedure, less you will go back to read about patterns and better your designs will become.

I think you have already realized that, this write-up is just yet another write-up on Design Patterns. Design patterns are not any technology. Design patterns are actually some patterns of thinking to come up with a flawless design of your software requirements. Here I will try to derive towards 2 of the most frequently used design patterns from some scenarios. I will not tell the name of the patterns at the beginning of the examples so that your reading does not become tempted by some existing theoretical knowledge on these design patterns. You will find the pattern name at the end of each discussion.

Pattern 1: Let’s say you have developed a new operating system for smart phones named “Smart OS”. Now you want general people to use smart phones using this operating system. What will you do? You will create a specification for the OS and some instructions to manufacture the phone.

Let’s assume all the people of the world are capable enough to manufacture a phone using a spec. So, if you want to sell the OS to 1000 persons, the solution is all of those 1000 persons will learn the spec, create the phone and use it. Does this sound practical? Now, if you release a new version of "Smart OS"? Will all of those 1000 person learn the new spec again and update their phone? What if you need more customers? It really sounds weird. What is the solution then? I am sure you all know the solution.

For your new OS you will release the spec to a phone manufacturing company or maybe you will form one of your own (Like Apple builds iPhone on top of their own OS). The company will manufacture the phone using your OS and the end users will buy this from the manufacturer. So what should be the expected benefits here?

1. Only the manufacturer company will need to know how to build the phone.
2. If the spec changes, the manufacturer will learn this and will distribute this to all the customers.
3. The price becomes less.
4. The end user will just use the phone and will not need to know how this is created.

I am sure already you are thinking the example is too obvious. But similar scenario comes when we develop software. Sometimes, the situations are not too obvious.

For example, you are required to develop an html report generator. If you jump start to the code every possibility is that, you will create a class named HtmlReportGenerator. That will have a method named GenerateReport. This method will implement the report generation functionality. The client app will create an instance of the Html report generator class and will use the GenerateReport function to create the report.  

Ok! That works fine. Now, your client wants a pdf report from you. What will you do?


Option 1: Create a PdfReportGenerator class.

Option 2: Re-factor the code. Remove HtmlReportGenerator. Instead create a general class
ReportGenerator. Create 2 methods. GenerateHtmlReport and GeneratePdfReport.
Option 3: Re-factor the code. Remove HtmlReportGenerator. Instead create a general class
ReportGenerator. Create the function GenerateReport as GenerateReport(ReportType rptType) taking report type as parameter.

Well, now if client wants reports in word format, xl format and few more formats, what will you do? Will you continue re-factoring? Or, will continue changing your report generator function? If you know the standard software design principles, these approaches will be a violation of Open-Close principle and Single Responsibility principle. Overall this will be a complete mess (BBOM).

Now, let's relate this with smart phone OS scenario. Though it was not very obvious, the Report generator example really has similarity with phone OS. So how could be the solution?

Simple! You create a company who will serve you the reports (As like mobile manufacturer company). You will serve data to the company and will tell which format of report you want. The company will deliver you the report. If new report format comes to the scenario, the company will learn how to do that and as a client you have to do nothing but ask for the correct type of report. See the code example below.

Now, using this design you have achieved the following benefits when a new report format needs to be added:

1. You do not need to modify the ReportGenerator base class.
2. You do not need to know how other report formats are handled.
3. You do not need to know how to get instance of the report generator of your desired format.
4. Your code becomes clean and free from BBOM.

Here you have one class named "ReportGeneratorCompany". As like the mobile phone manufacturer company for "Smart OS", ReportGeneratorCompany will know which format of report you need (The OS), It will create a report generator for you (perhaps the phone manufacturer team for "Smart OS" ) and the report generator will generate the report for you.

So, in both case ("Smart OS" and Report Generator), we saw how we can handle the scenario in smarter way. And believe me whenever, you do anything smartly in software, you are using a design pattern or as I said perhaps you are creating a new design pattern. I am sure many of you already have realized which design pattern I had used for report generator. Yes. This is Factory Pattern. Factory pattern is one of the widely used and simplest object creational design patterns. If the name of the "ReportGeneratorCompany" class were "ReportGeneratorFactory", it would have been more obvious to you. 

Here, the concept of the factory pattern is, you do not create instance of your object directly, rather you abstract away the instantiation logic in a factory class. When you need your object, you will ask the factory class and the factory class will provide you the instantiated object.

There are few other creational design patterns widely accepted as standards in software world. Those are: Factory Method, Abstract Factory, Builder, Prototype, etc.  In this write up we will discuss on 1 more creational design pattern. As before I would not tell now which one is that, rather I would like to derive to that pattern from design challenges.

Pattern 2: Let’s talk about the “Smart OS” again. Say in your smart phone OS you have some location specific features. For example you have location specific language support, you have location specific theme support, you have location specific hardware support etc. When you develop your OS, you listed out the features you are going to provide in your OS.  Now, the situation comes where few features will have different implementation based on your location. How will you handle this scenario?

Option 1: One way to solve this situation is have conditions in your Service provider classes (Language Service Provider, Theme Provider etc) and load the services based on the condition. In this case you have to provide the location information to the service providers.

What is your opinion on the above approach? I can see the following problem here:

1. If a new location needs to be added we will have to modify the Service classes, which is a clear violation of Open-Close Principle.
2. It is possible to load the OS with language from USA and theme from UK, which should not be allowed. 
3. Code is un-organized and no point of extension is available.

This type of scenario comes every now and then in our software development practices. Here requirement shows that few objects need to be instantiated as a group. That means, if the location is USA, you must load all the USA based services for your device. So, you must enforce this.

In our previous pattern (Factory pattern) we saw how we can separate the object creation logic from the client using a factory. Should we be able to do something similar for the location based services? Well Let us try.

1. First of all we will need to abstract away the definition of the services. So, let’s create 2 abstract classes for language service and theme service.
2. Create concrete classes for different services based on location, inheriting from the abstract service classes. For example UsaThemeService class and UkThemeService class inheriting from ThemeService abstract class.

3. Create an abstract factory class which will have abstract definition of the methods to create all the services that needs to be created based on location.
4. Create concrete factory classes inheriting from the abstract factory class to create specific location based services.

5. Create the class for LocationBasedService. This class will create all the services instances based on the location provided and will load the services.
6. The client will know the location and will create service factory of that specific location. Eventually client will load the services.

The above way of implementation can further be improved using few more patterns perhaps like strategy, DI etc. However, that is out of the scope of this discussion.

So, we have created the location based services for the “Smart OS” in a new approach than the 1st approach we described. The new approach tries to solve the design challenges that we had on the first approach.

1. Now, adding a new location based service is easy. You implement the services for the new location and as you already know what are the services you will need to implement from the LocationBasedServiceFactory you will not miss anyone. 
2. This implementation will enforce you to initiate all the services of the same location and will prohibit you to initiate services from mixed locations.
3. Service instantiation is abstracted away from the service itself, so, the client of the services do not need to know how to instantiate the services. 
4. Your code has become extensible and manageable.

I am sure many of you already realized which pattern I have used here. Yes the pattern is “Abstract Factory”.

I would like to summarize the abstract factory pattern as it is little more complicated than the simple factory pattern. Following are the participators in the abstract factory pattern.

AbstractFactory – Abstract class to declare operations (LocationBasedServiceFactory) that create abstract products (theme service, language service).

ConcreteFactory – Implements AbstractFactory class to implement operations to create concrete products (UsaBaseServiceFactory).

AbstractProduct - Declares an abstract class for a type of product objects (ThemeService).

Product - Defines a product to be created by the corresponding ConcreteFactory; it implements the AbstractProduct class.

Client - uses the abstract classes declared by the AbstractFactory and AbstractProduct classes.

In this article, I have derived to explain 2 creational design patterns with examples. My goal was to help you gearing your thinking process to solve a problem in more patterned way. I hope to write on few structural and behavioral patterns in coming days too. More practice you do to achieve the best architecture for your work, the better your architectures will be.

I will appreciate your comments and feedbacks on this.