I am getting error while updating a column in a table on while trigger configured

Question

I get this error:

Msg 217, Level 16, State 1, Procedure trg_upd, Line 7 [Batch Start Line 0]
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

when I use this trigger in my database:

CREATE TRIGGER trg_upd_inrt
ON [dbo].[tbl_A]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE A
    SET [Desc] = CASE i.[Desc]
                     WHEN 'HR DD' THEN 'HRDD'
                     WHEN 'H PP' THEN 'HPP'
                     WHEN 'RPT AR' THEN 'RPTARIN'
                     WHEN 'APPP PPLE' THEN 'APPLE'
                     WHEN 'HR DD' THEN 'HRDD'
                     ELSE Desc
                 END 
    FROM tbl_A a
    INNER JOIN deleted i ON a.id = i.id 
END

Answer 1

Unfortunately, SQL Server doesn't make it really simple to do what you want. You need to use an instead of trigger and update all columns:

CREATE TRIGGER trg_upd_inrt ON [dbo].[tbl_A] INSTEAD OF UPDATE
AS BEGIN
    SET NOCOUNT ON;

    UPDATE A
        SET [Desc] = CASE i.[Desc]
                         WHEN 'HR DD' THEN 'HRDD'
                         WHEN 'H PP' THEN 'HPP'
                         WHEN 'RPT AR' THEN 'RPTARIN'
                         WHEN 'APPP PPLE' THEN 'APPLE'
                         WHEN 'HR DD' THEN 'HRDD'
                         ELSE i.[Desc]
                     END,
            COL1 = i.Col1,
            COL2 = i.Col2,
            . . . -- all the rest of the columns that might be updated
        FROM tbl_A a INNER JOIN
             inserted i
             ON a.id = i.id ;

END;

Note that this uses the new value rather than the old value for the replacement. That makes sense to me. I assume that you really intend inserted rather than deleted because of the table alias.

If you do need both values, just include both in the FROM clause:

    FROM tbl_A a INNER JOIN
         inserted i
         ON a.id = i.id INNER JOIN
         deleted d
         ON d.id = d.id