This post is about something I come across more times than I would want to, and not even the least of people are repeatedly triggering my nerves with a thing like this. Anyway, I expected you not to, when you are a database developer in a multinational software development company.
Let’s cut to the chase: this post is about triggers. Database triggers. Very handy database objects that can perform a lot of useful tasks for you whenever you modify data in a table. I’m kidding. Of course, triggers are handy, but they can cause issues for performance, deadlocking, concurrency, and can even bring a not-too-busy database to a grinding halt when they are used wrong.
This post is not about that.
A few weeks ago, I received a call from one of our application managers. They told me they received some code to fix a bug in their application, and asked me if I would execute this code inside the application’s database:
CREATE TRIGGER _UpdatePlaceId
ON dbo.Appointments
FOR UPDATE
AS
IF UPDATE(PlaceId)
BEGIN
SET NOCOUNT ON;
DECLARE @appointmentId varchar(8)
DECLARE @placeId varchar(6)
SELECT
@placeId = inserted.PlaceId,
@appointmentId = inserted.AppointmentId
FROM
inserted
UPDATE dbo.AppointmentOccurence set X= @placeId WHERE AppointmentOccurenceId = @appointmentId;
END
GO
I wonder.. What would you think about this code? Is it ok or not? And why? Think about the impact this code would have if you are thinking like a business person.
I have changed the code a little bit by the way. Only the one who wrote it can tell it refers to something he wrote.
It took me one glance at this code to tell my application managers: “No, I am not going to run this code, because I think it is dangerous.” I am fortunate to work in a company where people trust eachother’s judgements, so they came to ask me why, and I told them: “Well, this code could work great, if you are sure you will always update one item at a time. But any time you to update multiple items at once, you (or in fact your users) will be screwed.
When updating multiple rows, the displayed code will insert only one of the occuring values into the @appointmentId and @placeId variables. Next, it will update the AppointmentOccurence table and set all the values in the two columns to those values. I know a little thing about business, and I doubt a business would want all their appointments to be at the same place at the same time. Take a look at this code again, and if you still can’t see what I mean, then please resign from your development job ;). Or start studying. One or the other.
This post showed you, triggers are dangerous stuff when you don’t know how to use them. In fact, I think any software developing company should have a policy like: “If you are contemplating using database triggers, first consult with your senior dba or database consultant.”
“Ok, we hear you, but if this developer said this code fixed a bug, how can we make it right?”
Well, I am glad you asked. It’s very easy as well. This developer made use of the “inserted” table. That’s one thing he did right. The solution is to join this table to the one that should be updated, and update the appropriate columns accordingly like this:
CREATE TRIGGER _UpdatePlaceId
ON dbo.Appointments
FOR UPDATE
AS
IF UPDATE(PlaceId)
BEGIN
SET NOCOUNT ON;
UPDATE AO
SET AO.X=inserted.PlaceId
FROM dbo.AppointmentOccurence AO
INNER JOIN inserted i
ON i.xId = AO.AppointmentId
END
GO
Is it me, or does that code look even smaller than the previous one?
Anyway. As I stated before, there are some uses for triggers that are useful. When you are working on a very busy application they could prevent an expensive round trip to the application server and back. But please, do not use triggers for elaborate processing work, and you would even prefer not to use the solution I posted as joining tables can be an expensive process. This post was about the correctness, the integrity of your data. Not about performance.
Keep in mind, when you start working with triggers, you will always have an overhead, performance or otherwize.
Anyway, never use variables in a trigger! Of course you could, if you know what you are doing, but, in general: “variables + trigger = wrong”.
.. Which makes me think about indexes. Indexes are great. They allow you to search for one item fast in a multi-million row table. Why not put 35 or more indexes on one table? Just to make everything go faster… What do you think?