CODEWITHSUNDEEP
sql-server-2008triggersasp.net-membership
mohsen dorparasti
mohsen dorparasti

Reputation: 8415

call aspnet_Delete_User SP in trigger

I have a trigger in students table that deletes related records from other tables when i delete a student. I want to delete student's membership data by calling aspnet_delete_user stored procedure in the trigger . but this works just if I delete one student . and if I remover multiple students in one query it doesn't work .

How to call this SP for multi-row operation trigger ?

Upvotes: 0

Views: 241

Answers (2)

Arion
Arion

Reputation: 31249

Maybe something like this:

CREATE TRIGGER TheNameOfTheTrigger
ON YourTableYouWantToTriggerOn
AFTER DELETE 
AS
   DECLARE @yourPrimaryKey int

    DECLARE delete_cursor CURSOR FOR 
    SELECT
        yourPrimaryKey
    FROM
        deleted

    OPEN delete_cursor

    FETCH NEXT FROM delete_cursor 
    INTO @yourPrimaryKey

    WHILE @@FETCH_STATUS = 0
    BEGIN
       EXEC aspnet_delete_user @yourPrimaryKey
       FETCH NEXT FROM delete_cursor INTO @yourPrimaryKey
    END

    CLOSE delete_cursor
    DEALLOCATE delete_cursor
GO

See more information here and here

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

You'd have to use a cursor or build a dynamic SQL string (which uses a cursor without saying so). Alternatively, you could copy the logic from the stored procedure and see if you can tailor it to become set-based - I haven't looked at the procedure, so I'm not sure if this is feasible, practical or even possible, but it's the first thing I try to do before adding cursors or dynamic SQL to a trigger.

For a cursor, something like this (I'm guessing you pass a GUID or something to the procedure, but I have no idea):

CREATE TRIGGER dbo.StudentAfterDelete
ON dbo.Students
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @MemberID UNIQUEIDENTIFIER;

  DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR SELECT MemberID FROM deleted;

  OPEN c;

  FETCH NEXT FROM c INTO @MemberID;

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    EXEC dbo.aspnet_delete_user @MemberID;

    FETCH NEXT FROM c INTO @MemberID;
  END

  CLOSE c;
  DEALLOCATE c;
END
GO

Dynamic SQL, same assumptions:

CREATE TRIGGER dbo.StudentAfterDelete
ON dbo.Students
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'';

  SELECT @sql += 'EXEC dbo.aspnet_delete_user ''' 
    + CONVERT(VARCHAR(36), MemberID) + ''';' FROM deleted;

  EXEC sp_executesql @sql;
END
GO

However including the missing information up front is more useful. Don't assume that everyone who works with SQL Server has any clue what aspnet_delete_user does.

Upvotes: 2

Related Questions