October 1, 2007 at 5:56 pm
I setup a trigger for insert/update to copy 1 field to another. Several 'advanced' users tested this on a test database with the host app and all worked as desired. We then put the trigger into production and the 'regular' users would use the app in a way us 'advanced' users never thought of. This started causing lock errors in the app user interface:
"Transaction (Process ID 123) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Is there a lock hint that has the affect of "wait until no lock exists then run trigger"? I would like any locks the app has to take precedence over the trigger.
Thanks.
October 1, 2007 at 6:46 pm
October 1, 2007 at 9:30 pm
Serg is right; what is your trigger doing? Is it calling a stored procedure or something ?Is it using spOACreate/xp_cmdshell? trying to email something? does it declare a variable? does it have a cursor? does it have a loop?
if it does any of the above, it's a hint that the trigger is not well designed, or being applied int he wrong situation.
post the trigger and we can help identify the issue.
Lowell
October 2, 2007 at 9:26 am
OK, here's what I'm trying to do:
When a new order line is added or qty_ordered is changed, copy qty_ordered to qty_to_ship and put an * in UDF_4
Only for 'O' type orders where status < '4' (char fields, both from order header)
CREATE TRIGGER CopyQtyToShip ON [dbo].[ORDLINE]
FOR INSERT, UPDATE
AS
if update(qty_ordered)
update ORDLINE
set ORDLINE.qty_to_ship = inserted.qty_ordered,
ORDLINE.udf_4 = '*'
from inserted inner join ORDHEADER
on ordheader.order_no = inserted.order_no
WHERE inserted.order_no = ordline.order_no and inserted.line_no = ordline.line_no
AND (ordheader.status < '4') AND (ordheader.type = 'O')
AND (ORDLINE.qty_ordered <> ORDLINE.qty_to_ship)
The lock seemed to occur because a user left for lunch with the app in edit mode on qty_to_ship.
Thanks for the suggestions.
October 2, 2007 at 4:20 pm
First you should separate the insert from the update.
Second you should check for @@rowcount at the begining of the trigger
Third you should join also in the update trigger with the deleted table and compare values before and after to make sure that update col1 = col1 is not triggering ... your trigger 😀
fourth make sure also that you are actually using indexes on that update query
hope this helps,
* Noel
October 2, 2007 at 4:37 pm
RD, does your application begin a transaction when user starts editing the cell?
_____________
Code for TallyGenerator
October 3, 2007 at 3:11 pm
I'm quite the junior scripter so any extra info would be appreciated.
1) are u saying use 2 triggers, 1 for update, 1 for insert?
2) where would @@rowcount be used? I have searched BOL and don't find examples of this used in triggers.
Thanks again.
October 3, 2007 at 4:00 pm
RD (10/3/2007)
I'm quite the junior scripter so any extra info would be appreciated.1) are u saying use 2 triggers, 1 for update, 1 for insert?
2) where would @@rowcount be used? I have searched BOL and don't find examples of this used in triggers.
Thanks again.
1) Yes because UPDATE () function makes sense on UPDATE triggers only 😉
2) in your trigger the first lines should be something like
declare @cnt int
set @cnt = @@rowcount
if @cnt = 0 RETURN
...
---body of the trigger
* Noel
October 3, 2007 at 4:37 pm
noeld (10/3/2007)
1) Yes because UPDATE () function makes sense on UPDATE triggers only 😉
Not quite true.
I would say absolutely false.
:Whistling:
_____________
Code for TallyGenerator
October 3, 2007 at 4:48 pm
RD,
Why you need to mention ORDHEADER twice?
inserted is ORDHEADER, right?
update ORDLINE
set ORDLINE.qty_to_ship = inserted.qty_ordered,
ORDLINE.udf_4 = '*'
from inserted
WHERE inserted.order_no = ordline.order_no and inserted.line_no = ordline.line_no
AND (inserted.status < '4') AND (inserted.type = 'O')
AND (ORDLINE.qty_ordered <> ORDLINE.qty_to_ship)
_____________
Code for TallyGenerator
October 3, 2007 at 4:59 pm
Sergiy (10/3/2007)
noeld (10/3/2007)
1) Yes because UPDATE () function makes sense on UPDATE triggers only 😉
Not quite true.
I would say absolutely false.
:Whistling:
Hehe, R E A L L Y ????
Can you show me a DELETE or INSERT statement that affects only some columns but not all columns in a row ???
I am really looking forward to your TSQL syntax on that one :D:D:D:D:D
* Noel
October 3, 2007 at 4:59 pm
The status and type only appear in the ordheader, not ordline. So I don't think Inserted would contain the status and type.
October 3, 2007 at 5:27 pm
noeld (10/3/2007)
Sergiy (10/3/2007)
noeld (10/3/2007)
1) Yes because UPDATE () function makes sense on UPDATE triggers only 😉
Not quite true.
I would say absolutely false.
:Whistling:
Hehe, R E A L L Y ????
Can you show me a DELETE or INSERT statement that affects only some columns but not all columns in a row ???
I am really looking forward to your TSQL syntax on that one :D:D:D:D:D
LMAOROTF, you just made my week Noeld :hehe:.
October 3, 2007 at 5:54 pm
Would not UPDATE() work in INSERT case?
It will work perfectly.
Yes, it will return "TRUE" for every column, but what does it change?
We still have to run the same script if the column is updated.
No matter how many other columns are updated as well.
So, what's a point to copy-paste the trigger into another trigger not having IF UPDATE() statement?
Should I list all disadvantages of "copy-paste" programming?
Or you gonna admit you both been dumb in this case?
_____________
Code for TallyGenerator
October 3, 2007 at 6:01 pm
All valid points Sergiy, but I must informed you that you've been OWNED. Big time.
Just kidding :w00t::D:hehe::cool::):D:P;).
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply