November 24, 2009 at 11:09 pm
I write an INSTEAD OF Trigger to do something before real deleting.
e.g. TableA has ID which is Primary Key
In the Trigger I write the following script to really delete.
declare @ID as INT;
Select @ID = ID
FROM Deleted
DELETE FROM TableA
WHERE ID = @ID
It seems OK without any error.
Once I tried to delete many records in the table by the following code
--------------------------------
DELETE FROM TableA
WHERE ID between 21 and 30
--------------------------------
The output message return
(10 row(s) affected)
But deleted records amount is only 1 record ?????????
If I continously run
--------------------------------
DELETE FROM TableA
WHERE ID between 21 and 30
--------------------------------
The output message return
(9 row(s) affected)
If I continously run
--------------------------------
DELETE FROM TableA
WHERE ID between 21 and 30
--------------------------------
The output message return
(8 row(s) affected)
The problem is the INSTEAD OF Trigger will delete once a record.
Because of
WHERE ID = @ID
So what can I do to solve the problem ????
My idea is to get list of deleted records in Trigger and delete by new condition such as
WHERE ID between @IDfrom and @IDTo
But I don't know how to accomplish it ??
Could you have any suggestions ???
November 25, 2009 at 1:01 am
The list of deleted rows is in the "deleted" table.
Your code
declare @ID as INT;
Select @ID = ID
FROM Deleted
DELETE FROM TableA
WHERE ID = @ID
is just retrieving one of those rows.
If you want to delete all the rows, then you will need to write the DELETE statement as a join to the "deleted" table.
November 25, 2009 at 4:02 am
Oh thanks so much.
I've already known the solution is to join as you recommended.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply