将更新尝试保存在更新触发器的临时表中

Question

查询是 - 任何用户都不应能够更改产品表的价格。应向用户显示一条消息。此外，所有更改价格的尝试都应保存在临时表中。

如何使用 Sql server 2005 中的更新触发器解决此问题？ 我已经尝试如下。它按预期工作，但也显示错误 -

事务在触发器中结束。该批次已中止。

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), @mNewPrice)

            print 'Can''t Change Price' 
                       ROLLBACK TRAN 

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

Answer 1

我不会将其编写为尝试回滚错误更改的 AFTER 触发器，而是将其作为 INSTEAD OF 触发器来处理，该触发器记录对价格的任何尝试更新，但允许更新其他列。

    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

您可以通过将 UPDATE 包装在 TRY/CATCH 块中来避免出现错误消息。这还允许您报告您不想抑制的错误。例如，您可能想尝试以下操作：

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

触发器确实存在另一个问题。它没有预料到使用单个 UPDATE 语句更新多行的情况。将 inserted 和 deleted 表中的列保存到变量时，您将丢失除结果集中返回的最后一行之外的所有行的信息。您可以通过将 SELECT、SET 和 INSERT 语句替换为单个 INSERT INTO...SELECT FROM 来避免这种情况。代码>声明。

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