Tuesday, September 11, 2018


When it comes to Triggers, I have a debugging story that suggests a good general principle (Thanks Steve Jones for hosting T-SQL Tuesdayon Triggers). On at least two separate occasions, someone has asked for help, because they are getting the error:
“SQL ERROR: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression”
The request is accompanied by a query with either a benign subquery (yup, could use a JOIN):

Update Sales
Set tax_rate = 0.07
Where state_id = (Select state_id from States where state_name = ‘Texas’)

Or, more confusingly, no subquery at all

Update Sales
Set tax_rate = 0.07
Where state_id = 42

The answer is to check for a Trigger that is coded to only handle a single record. There will be a query in the code looking up the key (for the single record) from Inserted or Deleted in a subquery (Question of the Day?).

The Trigger was usually written as part of an application and the developer knows, “We only Insert or Update a single record at a time.” That is, until a one-off data load is needed, or an application integration initiative changes the rules.

My general principle is that a Trigger should be written to operate on a set of records, no matter what the primary use case is. A corollary is that, in the case where the Trigger must be restricted to a single record, that it should validate that it only has one record, or raise a meaningful error when there are more. And do not forget there can be no records!

Epilogue

When doing technical interviews, I would occasionally as how to call a trigger. The usual answer was to the effect “I’ve used them in the past, but don’t recall the specifics.” People who knew their stuff answered straight up, but usually looked a bit askance at me…





Friday, April 30, 2010

I hold in my hand the final envelope...

This month of poetry is ending more with a whimper than a bang, but The Road Not Taken by Robert Frost still seems like a fitting poem on which to end. This exercise is clearly not on my beaten path, but I have enjoyed it and gotten some good feedback.

Putting these together made me think about other aspects of oral tradition. Punch lines of jokes and movie quotes are often part of a team's culture at work. Many of the better organizations have shared stories, of adversity, success, or special behaviors. One of my employers had a number of corporate stories that helped focus my efforts.

Think about the poems that influence your life. Ones that make you happy, help you to weather tough times, or remind you of special people.

If you applauded today's title, then I must quote Karnak, "May a weird holyman recite dirty limmericks to your sister."

Enjoy

Thursday, April 29, 2010

Fools Like Me

Today's poem is the famous Trees by Joyce Kilmer. This poem possibly understates the grandeur and beauty of trees -- particularly the larger and older ones. This poem has always struck me as something that should be sung like a minstral tune.

As a kid I climbed my fair share of trees. In the last few years, I've heard of Recreational tree climbing and thought I'd like to give it a try. It has not seemed to caught on in Texas, but I might get a chance when visiting Georgia.

Enjoy

Wednesday, April 28, 2010

Well, once more...

We could not have a National Poetry Month without including The Raven by Edgar Allan Poe. It is one of his most famous works and often studied in schools for its imagery and alliteration. Even young kids can enjoy a little scare from this poem.

During my 90's poetry kick this is one I committed to memory, for a number of reasons. I even goaded my cube-mate (we stared at each other all day) to learn it with me. We often started our day with a recitation and discussed unfamiliar words or the meaning of a stanza. The line "Respite, respite and nepenthe" has come in handy in a crossword puzzle or two.

I had forgotten that we studied this in school, until I remembered my teacher's antics. After reading a very dramatic and ominous "Nevermore" he said, "Actually, it was probably more like 'Nevermore, nevermore'" adopting a Polly Parrot voice.

Enjoy

Tuesday, April 27, 2010

Out with the bad air...

Today's poem is another selection from "A Children's Garden of Verses", Where Go the Boats by Robert Louis Stevenson. This poem makes me think of my favorite "Curious George" book where he folds newspapers into boats. I read the book and folded a paper for each of my daughter's classes as "mother reader".

Here is a fun link with Bullwinkle reciting the poem, with just a few problems.

Enjoy

Monday, April 26, 2010

Moon beams...

Today's poem was also shared with me, by a colleague, back in 1998. It is a piece of ancient Chinese poetry called Drinking Alone with the Moon by Li Bai. She learned it as part of applying to school in mainland China. It provided a differentiator besides grades and test scores.

The third and fourth lines --

Till, raising my cup, I asked the bright moon
To bring me my shadow and make us three.

are famous and she taught me how to pronounce them in Mandarin. I searched for good links and was thrilled with the one above. It has a pronunciation guide for each character -- read right to left and the title is included. My time in Taiwan equiped me to recognize the characters for 1, 3, and people (ren). This link gives a number of possible translations.

Enjoy

Sunday, April 25, 2010

Music when its good...

Today's selection is really a song, but it is poetic in its own right. I Love by Tom T. Hall is a song that I sang to both my girls when they were young, although I would substitute rootbeer for bourbon. I became a fan of his quite by accident. A cassette tape was left in my player in college. His style of story telling reminds me, a bit, of my grandfather's.

In using a song as poetry, I'm reminded of a "Saturday Night Live" skit. It had a high school english class with kids submitting 70's lyrics as original poetry.

Enjoy