CODEWITHSUNDEEP
asp.netsqlsql-servertriggers
user347284
user347284

Reputation:

Nested triggers in SQL

I have the following tables: replies and messages. I would like to imitate Facebook's behavior: when a message is deleted, all related replies are deleted as well. My tables look like this:

REPLIES
messageId                               replyId
6b61d107-dff3-4374-a3a2-75ac7478a2f2    865c873d-0210-482a-b8bd-371c4f07f0cf

MESSAGES
id                                      body
865c873d-0210-482a-b8bd-371c4f07f0cf    this is the reply
6b61d107-dff3-4374-a3a2-75ac7478a2f2    this is the message

I have created one first trigger, which works, which deletes the related lines in Replies when a message is deleted. I now would like to create another trigger which would delete the related message every time a line in Replies is deleted. Right now the replies become messages of their own, which doesn't make sense. Here's the second trigger:

CREATE TRIGGER TRG_DEL_MESSAGES
ON Replies
FOR DELETE
AS
DELETE FROM Messages WHERE id = (SELECT replyId FROM DELETED)

Which brings the following error when I try to delete something:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Apparently there's an infinite loop going and I think it is because the DELETED table is filled by data from the first trigger (which provoked the second). But really I'm not sure and I'd appreciate some help. Thanks!

Upvotes: 0

Views: 193

Answers (3)

Fergus Bown
Fergus Bown

Reputation: 1696

Will something like this work for you? Remove the recursion by just using a single trigger on Messages, that calculates all of the related messages up front, so you should get at most one nested call? It deletes all the related messages from both messages and replies

CREATE TRIGGER TRG_DEL_REPLIES
ON [Messages]
FOR DELETE
AS
BEGIN
    DECLARE @Related TABLE (MessageId uniqueidentifier)

    --get all related messages so that we don't recurse
    BEGIN
        WITH AllReplies (MessageId)
        AS
        (
            --Anchor
            SELECT  D.MessageId
            FROM    Deleted D
            UNION ALL
            --Recurse
            SELECT  R.ReplyId
            FROM    AllReplies AR
            JOIN    Replies R
                    ON AR.MessageId = R.MessageId
        )
        INSERT  INTO @Related
        SELECT  * 
        FROM    AllReplies
    END

    --delete the replies

    DELETE  R
    FROM    Replies R
    JOIN    @Related REL
            ON R.MessageId = REL.MessageId

    --delete the messages

    DELETE  M
    FROM    [Messages] M
    JOIN    @Related REL
            ON  REL.MessageId = M.MessageId 
    LEFT
    JOIN    DELETED D
            ON REL.MessageId = D.MessageId
    WHERE   D.MessageId IS NULL
END

To translate this into a stored proc as you wanted, i would do this, rather than a loop that does multiple separate deletes:

CREATE PROCEDURE DeleteMessageWithReplies(@messageId uniqueidentifier)
AS
BEGIN
    DECLARE @Related TABLE (MessageId uniqueidentifier)

    --get all related messages
    BEGIN
        WITH AllReplies (MessageId)
        AS
        (
            --Anchor
            SELECT  @messageId
            UNION ALL
            --Recurse
            SELECT  R.ReplyId
            FROM    AllReplies AR
            JOIN    Replies R
                    ON AR.MessageId = R.MessageId
        )
        INSERT  INTO @Related
        SELECT  * 
        FROM    AllReplies
    END

    --delete the replies

    DELETE  R
    FROM    Replies R
    JOIN    @Related REL
            ON R.MessageId = REL.MessageId

    --delete the messages that haven't already been deleted

    DELETE  M
    FROM    [Messages] M
    JOIN    @Related REL
            ON  REL.MessageId = M.MessageId 
END

Upvotes: 1

Paul Williams
Paul Williams

Reputation: 17030

I would recommend either generating all the deletes at once in a trigger, as Fergus Bown suggested, or moving your delete logic to a stored procedure call. In our application, we use the stored procedure approach for all CRUD operations (create, read, update, delete).

The downside is that a rookie support DBA might goof if they use SQL to delete a single Reply without deleting all the other Messages associated with it. But such a DBA ought to know to use the stored procedures instead (or get the SQL right in the first place).

Upvotes: 0

user347284
user347284

Reputation:

Thanks for your help Fergus, I appreciate it. However, as @Ben pointed, a stored procedure is easier and simpler to code. This is what I've just written, it could probably be improved but at least it works.

EXEC('CREATE PROCEDURE deleteMessageWithReplies(@messageId uniqueidentifier)
AS
BEGIN
    DECLARE @repliesCount int
    SELECT @repliesCount = (SELECT COUNT(*) FROM Replies WHERE messageId=@messageId)
    DECLARE @cpt int
SET @cpt = 0
DELETE FROM Messages WHERE id = @messageId
WHILE @cpt < @repliesCount
BEGIN
    DECLARE @replyId uniqueidentifier
    SELECT @replyId = (SELECT TOP 1 replyId FROM Replies WHERE messageId=@messageId)
    DELETE FROM Replies WHERE replyId = @replyId
    DELETE FROM Messages WHERE id=@replyId
    SET @cpt = @cpt+1
END
END')

Upvotes: 0

Related Questions