June 28, 2012 at 3:08 pm
I've been given an old script, and need to create it into a stored procedure. The script uses a cursor to delete from a table which has a trigger on it if something is deleted. The trigger does two things, inserts a record into the current database but a different table, and also executes a stored procedure on a linked server. I'm wondering if I need a cursor or while loop to do this because of the trigger?
Thanks!
June 28, 2012 at 3:27 pm
eagb (6/28/2012)
I've been given an old script, and need to create it into a stored procedure. The script uses a cursor to delete from a table which has a trigger on it if something is deleted. The trigger does two things, inserts a record into the current database but a different table, and also executes a stored procedure on a linked server. I'm wondering if I need a cursor or while loop to do this because of the trigger?Thanks!
Not much in the way of details but it sounds like that trigger is not able to handle multiple row deletes. This could be a real problem!!! The delete trigger should be able to handle this. Without some more details I couldn't possibly begin to assist with that but there should be no reason you need a script with a cursor to delete rows from a table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2012 at 3:35 pm
I believe it's just poorly written sql. Here is the cursor piece:
DECLAREcur_Persons CURSOR FOR
SELECTpersonid
FROM@deletelist;
OPENcur_Persons;
FETCH NEXT FROM cur_Persons INTO @personid;
WHILE (@@fetch_status = 0)
BEGIN
DELETEdbo.Persons
WHEREid = @personid
FETCH NEXT FROM cur_Persons INTO @personid
END;
CLOSE cur_Persons;
DEALLOCATE cur_Persons;
Here is the trigger piece:
ALTER TRIGGER [dbo].[tD_Persons]
ON [dbo].[Persons]
FOR DELETE
AS
DECLARE @ID int
DECLARE @ErrorNum int
insert Persons (
UpdateDtm,
UpdateUser,
PersonID,
PrefixName,
FirstName,
MiddleName,
LastName,
SuffixName,
temp_ContactID,
Action)
selectgetdate(),
suser_sname(),
ID,
PrefixName,
FirstName,
MiddleName,
LastName,
SuffixName,
temp_ContactID,
'D'
from deleted
/*
Maintain integrity
*/
SELECT @ID=ID FROM deleted
IF Exists (SELECT ID FROM linkedserver.database.dbo.table WHERE PersonID=@ID)
BEGIN
Exec linkedserver.database.dbo.spDeleteUser_PersonID @ID
SET @ErrorNum = @@Error
INSERT INTO Temp_PersonsTrigger_ErrorLog
(ErrorNumber, ErrorTime, PersonID)
VALUES (@ErrorNum, GETDATE(), @ID)
END
I'm just wondering if I'm missing something. I would think I could just delete from the table. :ermm:
June 28, 2012 at 4:17 pm
is this line in the trigger?? from your post i think so:
SELECT @ID=ID FROM deleted
if so the trigger looks to only be able to handle one row being deleted and the execution of the linked server SP needs to be rewritten to handle more than one delete at a time.
you may rewrite every thing with the deleted ID's being inserted into a table variable and pass the table var into the stored procedure on the linked server? would be able to handle as many rows as you put to it and you could get rid of your cursor with the proper joins to the table var on the linked server.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
June 28, 2012 at 4:50 pm
Yes you are correct it's part of the trigger. Thank you for helping me see that! Unfortunately I can only suggest they change the linked database so most likely this script won't have much room for improvement.
June 28, 2012 at 5:05 pm
eagb (6/28/2012)
Yes you are correct it's part of the trigger. Thank you for helping me see that! Unfortunately I can only suggest they change the linked database so most likely this script won't have much room for improvement.
aren't restrictions on what you can change nice? the question becomes at what point will performance suffer to where you can change the trigger. once you can change the trigger the bad programming that requires the cursor can be eliminated.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
June 29, 2012 at 6:26 am
eagb (6/28/2012)
Yes you are correct it's part of the trigger. Thank you for helping me see that! Unfortunately I can only suggest they change the linked database so most likely this script won't have much room for improvement.
Check the code in the linked server procedure "spDeleteUser_PersonID"
If the procedure simply deletes the person from the linked server database, you can implement that code completely in your trigger instead of calling the SP to do that
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2012 at 6:59 am
You can delete as many rows from the table as you like. The trigger fires FOR EACH ROW, so is fine handling one record. Test it, you'll see. Just do a delete from table for 100 rows and watch it.
June 29, 2012 at 7:42 am
Scorpion_66 (6/29/2012)
You can delete as many rows from the table as you like. The trigger fires FOR EACH ROW, so is fine handling one record. Test it, you'll see. Just do a delete from table for 100 rows and watch it.
I don't think so, not at least in SQL Server
The trigger does not fire once for each row but once for each statement
A single delete statement fires the trigger only once
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2012 at 10:57 am
Scorpion_66 (6/29/2012)
You can delete as many rows from the table as you like. The trigger fires FOR EACH ROW, so is fine handling one record. Test it, you'll see. Just do a delete from table for 100 rows and watch it.
Actually, in SQL Server, INSERT/UPDATE/DELETE triggers fire once meaning it is important to ensure that your code works properly for a single record insert and for a multi-record insert.
Oracle and Interbase can fire a trigger for each row. I'm not sure how any of the others work, so I won't even hazard a guess for them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply