May 1, 2008 at 7:47 pm
In MS SQL Server Management Studio, I want to delete some rows at the same time in the table which is created a delete trigger before the trigger is executed. However, i can't do it because SQL Server Management Studio will not delete them concurrently but it will delete row by row, e.g when i want to delete 3 rows on the table, MS SQL Server Management Studio will get the first row to delete and then execute trigger. Next, delete the second row and execute the trigger. And continue as like that to come last row.
Are there anyone know how to change this option in MS SQL Server Managerment Studio?
Thanks for any reply!
May 1, 2008 at 9:09 pm
Yeah, run your delete using T-SQL, beginning with a [font="Courier New"]DISABLE TRIGGER[/font] statement and ending with an [font="Courier New"]ENABLE TRIGGER[/font] statement. The trigger will be disabled for ALL connections of course, so use with care if there are other users/applications connected.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 1, 2008 at 10:21 pm
Yes, delete them all in one SQL statement instead of deleting one row per statement.
Unlike Oracle, SQL triggers fire per statement, and not per-row.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 1:09 am
Yes. Thanks so much for your replys, Scott Duncan and Ten Centuries! Maybe i should explain more details about my problem.
For example:
Table's Name is TABLE1:
[Code] varchar(1), not null
[IngNo] int, not null
[Desc] varchar(30), allow null
(two field [Code] and [IngNo] are a primary key in TABLE1)
*Sample:
[Code] [IngNo] [Desc]
a 1 Type 1
a 2 Type 2 (*)
a 3 Type 3 (*)
a 4 Type 4
a 5 Type 5 (*)
a 6 Type 6 (*)
b 1 Type 1 (*)
b 2 Type 2
b 3 Type 3
b 4 Type 4 (*)
b 5 Type 5 (*)
b 6 Type 6
I want after i delete some rows in TABLE1 (marked
(*)) then the values of [Ingno] in each the code will reorder incremently as follows:
a 1 Type 1
a 2 Type 4
b 1 Type 2
b 2 Type 3
b 3 Type 6
To solve this problem, i use a Delete Trigger.
However, when i delete some rows (marked rows (*)) in MS SQL Server Management Studio (but no error happens if i delete them in query Analyzer), SQL Server gives an error as follows:
"No rows were deleted.
A problem occurred attempting to delete row 5.
Error source: Microsoft.visualStudio.DataTools
Error Message: The updated row has changed or been deleted since data was last retrieved.
Correct the errors and attempt to delete the row again or press ESC to cancel the change(s)."
That is because SQL Server doesn't delete all the rows concurrently before executing the trigger. It deletes the first row and executing the trigger (e.i the values of ingno of a are assigned again 1,2,3,4,5 ). Next, the second row and executing the trigger (e.i the values of ingno of a are assigned again 1,2,3,4). Next, the third row and executing the trigger (e.i the values of ingno of a are assigned again 1,2,3)--> at here, SQL server gives the error because it can't find the row has code='a' and ingno=5.
So i want the trigger is only executed when all the rows are deleted. If we use disable trigger during delete the rows, we can't get row which is deleted and saved in DELETED table. Therefore, we can't assigned again the values of Ingno as expecting.
Do you have any solution to solve my problem?
Thanks for your help!
May 2, 2008 at 6:00 am
Post the trigger code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 8:34 am
Wouldn't it be easier to use Row_Number() to rebuild the rows, instead of whatever cursor or loop you are using in your trigger to increment the numbers?
create trigger Table_Delete on dbo.Table
after delete
as
create table #NewIng (
Code char(1),
IngNo int,
[Desc] varchar(20))
insert into #NewIng (Code, IngNo, [Desc])
select Code, Row_Number() over (partition by code order by [Desc]), [Desc]
from dbo.Table
where code in
(select code
from deleted)
and ingno not in
(select ingno
from deleted)
update dbo.Table
set IngNo = #NewIng.IngNo
from #NewIng
inner join dbo.Table
on #NewIng.code = Table.code
and #NewIng.[Desc] = Table.[Desc]
Something like that shouldn't have the type of error you're running into, which isn't caused by the way Management Studio deletes stuff, it's caused by the code in the trigger.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2008 at 10:23 am
Thanks for your reply, Ten Centuries!:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply