CODEWITHSUNDEEP
sqlazureazure-sql-database
TChadwick
TChadwick

Reputation: 878

SQL Delete Statement only deleting last row

I am attempting to do some batch deletions using Azure's SQL Database, I run a statement as follows:

delete from DbRoles where AccountID = 41;

One would expect that all rows where AccountID is 41, however it only deletes that last matching row from the table. I have found this to be true whether using a regular "where" statement as well as using subqueries like

 where Id in (select Id from DbRoles where AccountID = 41)

(And Id in (41, 42, 43, ... etc) also fails) Does anyone have any ideas why this might be?

----- EDIT -----

More info to answer some questions in the comments: The Roles table does indeed have a trigger on delete (thanks for the tip @Steve Ford) We have a Closure table connected to the Roles table, and it gets updated via triggers when actions occur in the roles table. One of which is the delete trigger, which goes as follows:

CREATE TRIGGER [tr_dbRole_delete]
    ON [dbo].[DbRoles]
    Instead of DELETE
    AS
    BEGIN

         declare @roleID int;

         select @roleID = Id from deleted

         --delete self closure
         delete from DbRoleClosures
         where childID = @roleID and parentID = @roleID

         --delete role
        delete from DbRoles
        where Id = @roleID

    END

I think the "Instead" statement might be my culprit, can anyone verify?

Upvotes: 0

Views: 185

Answers (1)

Steve Ford
Steve Ford

Reputation: 7763

Your trigger is only deleting one row because of this statement:

select @roleID = Id from deleted

When you delete multiple rows @roleID will be set to one of the Id's depending upon which order SQL Server queried your deleted table.

Trigger should be this:

CREATE TRIGGER [tr_dbRole_delete]
ON [dbo].[DbRoles]
Instead of DELETE
AS
BEGIN


     --delete self closure
     delete from DbRoleClosures
     where childID = parentId AND
           childID in (SELECT Id from deleted)

     --delete role
    delete from DbRoles
    where Id in (SELECT Id from deleted)

END

Upvotes: 1

Related Questions