Save Update attempts in temp table in Update Trigger

The query is - No user should be able to change prices of the products Table. A msg shold be displayed to the user. also all attempts to change the price should be saved in temp table.

How to solve this using update trigger in Sql server 2005? I have tried as below. It is working as expected but also showing an error -

The transaction ended in the trigger. The batch has been aborted.

ALTER TRIGGER tr_Products_U ON dbo.ProductDemo AFTER UPDATE
AS
    DECLARE @Data VARCHAR(200),
            @sProductName NVARCHAR(40), 
            @mOldPrice MONEY, 
            @mNewPrice MONEY,
            @ProductId int

    IF UPDATE(ListPrice)
    BEGIN
        SELECT 
            @ProductId = d.ProductID,    
            @sProductName = d.Name,
            @mOldPrice = d.ListPrice,
            @mNewPrice = i.ListPrice
        FROM
            inserted i INNER JOIN deleted d ON i.ProductID = d.ProductID 

        SET @Data = 'Tried to update the price of ' + @sProductName 
            + '  [ProductID :' + CONVERT(VARCHAR(10), @ProductId) + '] '
            + ' from '
            + CONVERT(VARCHAR(10), @mOldPrice) 
            + ' to ' + CONVERT(VARCHAR(10), @mN开发者_开发知识库ewPrice)

            print 'Can''t Change Price' 
                       ROLLBACK TRAN 

                        INSERT INTO #UpdateLIstPrices  
                        VALUES (@Data);         
     END
    RETURN
GO

---

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

---

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