Save Update attempts in temp table in Update Trigger

Question

The query is - how to Save Update attempts in temp table in Update Trigger

Answer 1

Rather than writing this as an AFTER trigger that tries to rollback the bad change, I would approach this as an INSTEAD OF trigger that logs any attempted update to price but allows updates to other columns.

    ALTER TRIGGER tr_Products_U ON dbo.ProductDemo INSTEAD OF UPDATE
    AS   
        IF UPDATE(ListPrice)
        BEGIN    
            PRINT 'Can''t Change Price' 

            INSERT INTO #UpdateLIstPrices  
                SELECT 'Tried to update the price of ' + d.Name 
                       + '  [ProductID :' + CONVERT(VARCHAR(10), d.ProductId) + '] '
                       + ' from '
                       + CONVERT(VARCHAR(10), d.ListPrice) 
                       + ' to ' + CONVERT(VARCHAR(10), i.ListPrice)
                    FROM inserted i
                        INNER JOIN deleted d 
                            ON i.ProductID = d.ProductID   
         END
         ELSE
         BEGIN
             UPDATE pd
                 SET Name = i.Name
                     /*, other columns as needed */
                 FROM inserted i
                     INNER JOIN dbo.ProductDemo pd
                         ON i.ProductID = pd.ProductID
         END
        RETURN
    GO

Answer 2

You can avoid the error message by wrapping your UPDATE in a TRY/CATCH block. This will also allow you to report errors that you do not want to suppress. For example, you may want to try this:

BEGIN TRY
    UPDATE ProductDemo
    SET ListPrice = 100.00
    WHERE ProductID = 3
END TRY

BEGIN CATCH
    IF ERROR_NUMBER() <> 3609
    BEGIN
        DECLARE @errormessage nvarchar(500)
        DECLARE @errorstate INT
        DECLARE @errorseverity INT

        SET @errormessage = 'Error ' + CAST(error_number() AS nvarchar) + ':  ' + error_message()
        SET @errorstate = error_state()
        SET @errorseverity = error_severity()

        RAISERROR (@errormessage, @errorseverity, @errorstate)
    END
END CATCH

You do have another issue with the trigger. It doesn't anticipate the situation where multiple rows are updated with a single UPDATE statement. When you save columns from the inserted and deleted tables to variables, you will lose information from all rows except the last rows returned in the result set. You can avoid this situation by replacing your SELECT, SET and INSERT statements with a single INSERT INTO... SELECT FROM statement.

PRINT 'Can''t Change Price' 

INSERT INTO #UpdateLIstPrices  
SELECT 'Tried to update the price of ' + d.Name 
            + '  [ProductID :' + CONVERT(VARCHAR(10), d.ProductId) + '] '
            + ' from '
            + CONVERT(VARCHAR(10), d.ListPrice) 
            + ' to ' + CONVERT(VARCHAR(10), i.ListPrice)
FROM inserted i
INNER JOIN deleted d ON i.ProductID = d.ProductID