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…