March 24, 2016 at 1:27 pm
I don't know of a way to bypass the design of the table without modifying the design.
The real question is why you'd want to bypass the structure of the table. The computed column is computed and (I presume) exists to maintain a consistent value. Why would you want to update it to violate the rules of the design?
March 24, 2016 at 1:54 pm
you cannot update a calculated column.
the other thing is your trigger design!
if i see a variable declared in a trigger, i immediately expect it's not designed correctly. you are grabbing multiple rows, and assigning it to the status variables, but what if there was more than one?
it is not designed to handle multiple rows, which is an extreme no no, ESPECIALLY for an instead of trigger; you are guaranteed to lose rows of data with the every multi row insert.
one of the folks on here retells the story how a company went out of business due to not addressing this exact problem.
don't use a trigger unless you have to, and don't try to modify calculated columns.
if you do really need a trigger, make sure it uses the DELETED and INSERTED pseudo tables to do set based operations for all rows int he transaction.
Lowell
March 24, 2016 at 2:13 pm
Without looking at the actual script, just commenting on the generic process.
One way to achieve what you want is to use a view instead of a table per the "parent" level. The base parent table would have all normal columns but not the status, the view would add the status. On that view, you can then define INSTEAD OF triggers (I'd go with one each for insert, update, and delete) that propagates every intended change to the correct actual changes on both parent and child tables.
If your system has high concurrency, then ensure that all these triggers access the involved tables in the correct order, to minimize the chance of deadlocks.
March 24, 2016 at 3:20 pm
The intent was to avoid having to do a complete code and proc sweep to implement this change. I'm not trying to avoid the structure of the table, it's just that the query optimizer isn't smart enough to realize that the INSTEAD OF trigger is properly handling the status ID, so that the insert/update would work fine, if the error wasn't being thrown before the query plan was fully developed.
The trigger does use the INSERTED table, and it does update ALL rows in the child table, as designed. The script parts shown are not the actual complete scripts, in order to protect my client's proprietary data structures.
I could do this with a view and the same trigger on the view, I was just hoping to avoid those extra steps, which would likely require a code review anyway.
Instead, I've modified the stored procedures, and I ALWAYS avoid using triggers, especially instead of triggers, whenever possible, but I also like to have business logic enforced in the database so that adhoc queries apply the same rules consistently and analysts don't have to read application code to understand the business logic.
March 24, 2016 at 3:36 pm
bartedgerton (3/24/2016)
it's just that the query optimizer isn't smart enough to realize that the INSTEAD OF trigger is properly handling the status ID, so that the insert/update would work fine, if the error wasn't being thrown before the query plan was fully developed.
It's not the QO. The error's thrown early in the parsing/binding phase, before the QO's even invoked. Side effect of an interpreted language.
The algebriser is checking the insert statement against the table definition, nothing fancier than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply