You’re going to love this one!
Some of our integrations tests use AdventureWorks 2000. One developer’s test updated a Sales.SalesOrderDetail. The test suddenly blew up with a SQL Server 2005 error that complained about exceeding the size limit of a TinyInt.
The error didn’t say where this TinyInt lived or what the column was called. The best we could tell was that the exception occurred while updating Sales.SalesOrderDetail.
SalesOrderDetail has a TinyInt column, “LineNumber”, which was not jeopardized by the update.
Aside: I have a different AdventureWorks db on my machine that doesn’t have “LineNumber” or a TinyInt but it does have, “OrderQty”, which is a SmallInt. I would have suspected that (“SmallInt, TinyInt, what’s the difference?”); there would be no problem there either.
No other developer could reproduce the SQL exception.
After hours of hair-pulling, the developer found that there is an update trigger on SalesOrderDetail that updates the parent Sales.SalesOrderHeader. SalesOrderHeader has an update trigger that increments SalesOrderHeader.RevisionNumber … which is a TinyInt.
Over the course of many tests, that RevisionNumber kept creeping up … until it hit 255. Each developer has his own AdventureWorks copy so no surprise no one else could reproduce it.
You can blame the victim if you wish; should have had adequate rollback logic all along.
But I have to admire the genius who decided that you’d never exceed 255 updates of a SalesOrderHeader in a test database.
Let’s thank SQL Server for throwing an exception on exceeding the max for a TinyInt without telling us the name of the column or table involved.
Kudos also to the other evil genius in this mess who wrote a trigger on one table that fires a trigger on another table to update the RevisionNumber … and didn’t bother to check the type or implement exception logic that would help clarify the problem.
There must be an award for this kind of thing.
Lesson #1: If you are using AdventureWorks 2000, be sure to reset all SalesOrderHeader.RevisionNumber values to 0. It’s too late to mess with the schema.
Lesson #2: Hey, SQL Server folks, how about a little more help in exception messages?
Lesson #3: Please write good exception messages yourself … messages that would give the poor sod who received it a fighting chance of understanding the problem. Extra credit if you suggest how to fix it.
Lesson #4: Business logic in the database is killing us out here. You DBAs have got to be more helpful.
Lesson #5: Stop trying to squeeze every last byte out of the schema. TinyInt should be used only in the most extreme circumstances and never where it could be incremented.
This only gives weight to my oft-repeated contention that "database triggers are the work of the devil".
ReplyDelete