Update anamoly due to trigger?
-
I have a situation that is simlar to one described below. Two tables:
Table byState (
state_code int
amount int,
lock int
)
Table byCounty (
county_code int,
amount int,
lock int
)
An update trigger updates byState.amount = sum(byCounty.amount)
An update statement tries to set all byCounty.lock=1 whenever set byState.lock=1 - This fails with the 'Subquery returned more than 1 value' error; due the update trigger in place on byCounty
However when I defined the update trigger I specified it to update - IF UPDATE (byCounty.amount) i.e. update the State totalonly when the county amount is entered.
My question is - an update trigger on one field of a table is preventing updates to the other field i.e. byCounty.lock
How to get around this?
When I disable the trigger then the update happens just fine.
-
Would you please provide the complete DDL for the trigger?
-
Rohit,
the problem seems to be that your trigger can't handle the update of several records at once. This is a common problem when people start using triggers. If you post the Trigger code we can help you further.
Markus
[font="Verdana"]Markus Bohse[/font]
-
The trigger is of the form : ( Include a state_code column in byCounty)
CREATE trigger [dbo].[tr_cty_st]
on [dbo].[byCty]
after insert,update
as if update(amount)
set nocount on
update bys
set bys.amount=
(select sum(byc.amount)
from byCounty byc
where byc.state_code=bys.state_code
)
from byState bys
join INSERTED i
on bys.state_code=i.state_code
set nocount off
I have tested this trigger to ensure it is working and only firing when the byCounty.amoutn column is updated.
Do you guys still think that the trigger is causingt he update to byCounty.lock to fail?
~Rohit
-
Got a reply on Google groups that solved my issue.
The problem was that the only command controlled by the "if update(amount)" is the "set
nocount on"
I took the set nocount statements out and the updates are working properly.
So much for best practices - guess they can blindside you some time if you apply them across the board
I am not even sure that the 'set nocount on\off' statements were helping the trigger 'performance' if any
Thanks to all of you for your inputs.
~Rohit
-
While that will work, it's typically preferred to use BEGIN/END to define the statement block. That way, if you decide to add additional code, it won't break again. Straight from BOL:
- Boolean_expression
Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.
- { sql_statement | statement_block }
Is any Transact-SQL statement or statement grouping as defined by using a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement.
To define a statement block, use the control-of-flow keywords BEGIN and END. CREATE TABLE or SELECT INTO statements must refer to the same table name when the CREATE TABLE or SELECT INTO statements are used in both the IF and ELSE areas of the IF...ELSE block.
-
David is correct...
First, move the SET NOCOUNT ON statement to before the IF. There's a lot more at stake than just performance if a GUI is involved (pevents false returns that can be interpreted as an error).
Second, multi-statement IF muste be in the form of
IF somecondition
BEGIN
... do something...
... do something else
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply