Don’t forget the food drive tomorrow for SQL Saturday #52 in Colorado. Please bring food, or even clothing/blankets, that can be donated to local people.
I’ll be taking a portion back to the food back for my local area, and we have a number of people from Boulder looking to do the same. Give back and help your local community.
Not quite a day of rest, likely some chores in advance of another busy weekend at the ranch.
I’m taking today off for a few reasons. Originally I had planned it as my kids were out of school and we had booked a trip to the Denver Mint. That’s exciting enough, and I was glad this was scheduled.
However as this week has grown closer, I’ve been stressed. Life is busy, one of the busiest times my wife and I have had in a long time with work, kids, various sports, scouts, etc. It’s a little overwhelming, and we still have a number of chores that we need to get done before winter. We’re also having a few people over this weekend, so I’m slightly worried about cleaning up as well.
So I need a day off.
I’ll be building some fence in the morning, and then taking the kids to the Mint, and perhaps a movie before the Friday night speaker’s dinner.
How often have you seen someone write a DML trigger like this:
declare @id int
select @id = id from inserted
update xx set yyy = zz
where id = @id
return
There seems to be this common misconception that a trigger fires for each change in a row (insert/update/delete), and that’s not true. As noted in Books Online, triggers fire once for the insert/update/delete. Typically this is an implicit transaction for the statement. If you have multiple statements inside an explicit transaction, the trigger fires once for each insert/update/delete statement in the transaction.
That means that if I change two rows and I have the trigger above, I won’t get the behavior I expect from the trigger. Let’s say that I want to update my inventory table each time I change an order. Imagine that I have this in my orders table:
and this in my inventory table.
If I now write this trigger:
declare @qty int
, @product int
select @qty = a.qty – b.qty
from inserted a
inner join deleted b
on a.orderid = b.orderid
select @product = productid from inserted
update inventory
set onhand = onhand – @qty
where productid = @product
return
and execute this:
I will find that my inventory table looks like this:
That appears to work, but what happens if we execute this?
Then we find that the inventory is
In this case productID 1 has had its inventory reduced by 1, but not product ID 2. Why not?
When the trigger fires, there are actually this data in the tables:
Inserted
OrderID OrderDate CustomerID int qty productid
——– ———- ————– ———– ———–
59CD… 2010-09-22 3 3 1
01B6… 2010-09-22 2 2 2
and deleted
OrderID OrderDate CustomerID int qty productid
——– ———- ————– ———– ———–
59CD85… 2010-09-22 2 2 1
01B66E… 2010-09-22 2 1 2
However the trigger, in setting the variables to the result of a query could have picked either of the rows, but only one row. SQL Server doesn’t guarantee order without an ORDER BY, so either product ID could have been chosen. As a result, only one of the products had the inventory updated.
A proper trigger would look like this
update inventory
set onhand = onhand – ( a.qty – b.qty)
from inserted a
inner join deleted b
on a.orderid = b.orderid
where inventory.productid = i.productid
return
Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if you always just update single rows, coding this way will prevent issues if there is a multiple row change.
When Microsoft implemented Connect, I thought it was a great idea. It was a way for real users to submit bugs, and others to see those bugs, voting on them if they thought they were important. It would help Microsoft determine what features and bugs are important and perhaps allocate resources accordingly. However there was a fundamental problem with the system. People would see individual items, and could vote for them, but wouldn’t have an idea of what other items might be listed.
The work on SQL 11 is underway, and recently I got a note from Itzik Ben-Gan asking people to vote for windowing enhancements to the T-SQL language. I’m not sure exactly of all the places that these are useful, but Itzik is one of the smartest people I know and I tend to believe that if he finds these enhancements useful, they are likely going to make T-SQL easier to work with.
But are these items a priority? I am sure they are valuable, but are they more valuable than CREATE or REPLACE? IS it more of a priority than allowing SSMS add-ins? There are any number of enhancements that are listed, but most of us don’t have the time to dig through them all, or even try to determine how important they might be when weighed against other items.
Microsoft can do what they want, and they need to keep one eye on the sales generated from new features. However I wish that they’d reserve a slice of their development efforts for older features and get some community help in choosing which items to work on. I’d love to see a list of the items they are considering, maybe the top 20 features, and let us add votes to pick the 10 they can work on.
We may not sign the purchase orders, but us DBAs really like SQL Server and would appreciate improvements that make our jobs easier.
Steve Jones
PS: Here are Itzik’s items:
