March 21, 2003 at 2:08 pm
We manually updated about 1000 records at a time and we have an update trigger that isnt working on about 5-10 records. It may be that the trigger cannot keep up. Any suggestions?
Thanks.
Andrew J. Hahn
March 22, 2003 at 1:56 am
Did you do the updates as 1000 single row updates, or one update that updated 1000 rows at once? What does your trigger do?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 22, 2003 at 12:56 pm
Hi Chris,
We do the updates all at once. For example UPDATE tblItem
SET ItemStatus = 'Complete'
WHERE ItemID IN (
'4322342',
'3453433',
.
.
.
)
The trigger checks to see if the items meet a few criteria and then kicks off a stored procedure.
It kicks off the stored procedure toward the end of the trigger, such as:
'exec ItemTrigger @ItemID'
We use SQL Server 7.
Thanks!
Andrew J. Hahn
Andrew J. Hahn
March 22, 2003 at 2:46 pm
Do you run this procedure for every updated row? In a cursor or how do you exeute it per row? The trigger should definately be able to keep up, there is no concept of it doing otherwise. Are you sure that there is no error in the code, in some condition check or something?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 24, 2003 at 3:37 pm
The Update trigger should get fired off 1000 times if I update 1000 rows at the same time.
In each instance of the trigger, it executes the stored proc for a single record. So, thate proc would get executed 1000 times if I updated 1000 rows.
There could very well be a code error causing the trigger to fail. Its just that the trigger works if I manually run the ones that failed initially. Nothing has changed except I am running the trigger with a lighter load.
Andrew J. Hahn
Andrew J. Hahn
March 24, 2003 at 4:21 pm
How big / complex is the stored procedure? Does it change the trigger table?
March 25, 2003 at 1:14 am
quote:
The Update trigger should get fired off 1000 times if I update 1000 rows at the same time.
Actually, no, this is not correct. If your statement updates 1000 rows, then your trigger will fire once, and the virtual tables deleted and inserted will contain 1000 rows each, deleted will contain the old rows (before the update) and the inserted table will contain the new rows (after the update).
So you need to figure out how you want to add your business logic to these 1000 rows at once, or you must execute your SP 1000 times from your trigger (not recommended as this will hurt performance).
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 7:16 am
The stored proc is about 400 lines and it is not too complex. There are several SELECTS and some logic done in there. The sp never changes the trigger table, other than passing it variables.
Oh, so if I do an update like the one I demonstrated above, the trigger fires only once and I would need to either change logic or do a series of single updates? That makes sense..
Thanks!
Andrew J. Hahn
Andrew J. Hahn
March 25, 2003 at 7:22 am
quote:
Oh, so if I do an update like the one I demonstrated above, the trigger fires only once and I would need to either change logic or do a series of single updates? That makes sense..
Yes, absolutely correct.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 27, 2003 at 3:18 am
Triggers in SQL Server fire on a statement basis (means that for each statement you issue, even if it affects multiple rows), and not on a row basis, and always after the statement was executed.
To enable a trigger to execute for example a stored procedure for each row that was touched by the statement, you'll have to use a cursor looping the "inserted" pseudo table.
This table is a shadow copy of the table the trigger is defined on, and contains all the new values for the rows affected by your statement. See also "deleted" pseudo table.
If your updates are affecting a large number of records, I'd generally recommend not to use this technique, but to loop over the records you want to process manually and execute your procedure.
March 27, 2003 at 4:34 am
Can you post the trigger code so we can get a better idea of what it is doing. It should have no issues keeping up but depending on the way it is put together.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply