March 24, 2016 at 1:16 pm
We are doing some refactoring of our database, and we are redefining a status column in a parent table to now be on the child table, and the parent table status column is to be changed to a computed column that would display status based on the child column statuses.
However, we would still like to be able to be able to update the parent's computed column, but have that status actually update the related child rows status instead, and we would expect the computed column to show the new summarized status value.
I've created a function to return the computed status on the parent table, and that's working fine.
I also created an INSTEAD OF UPDATE, INSERT trigger that I would like to accept a status column value, but then I run an update statement on the child table in the trigger.
CREATE TRIGGER [dbo].[trInsteadOf]
ON [dbo].[myParentTable]
INSTEAD OF INSERT, UPDATE
AS BEGIN
DECLARE @StatusID INTEGER
,@RecordID INTEGER;
SELECT
@StatusID = [StatusID]
,@RecordID = [RecordID]
FROM [inserted];
UPDATE [dbo].[myChildTable]
SET [StatusID] = @StatusID
WHERE [ParentRecordID] = @RecordID;
MERGE [dbo].[myParentTable] AS [target]
USING [inserted] AS [source]
ON ([target].[RecordID] = [source].[RecordID])
WHEN MATCHED THEN
UPDATE
SET [target]...
WHEN NOT MATCHED THEN
INSERT (...
However, SQL Server doesn't like an insert/update on a calculated column, so it blows up before the INSTEAD OF trigger even starts with the error:
Msg 271, Level 16, State 1, Line 1
The column "StatusID" cannot be modified because it is either a computed column or is the result of a UNION operator.
Is there a way to bypass the check of the table structure when an insert statement is executed, or some other way to avoid the error to get the desired result?
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply