SQL Server Error Messages - Msg 140

Error Message

Server: Msg 140, Level 15, State 1, Line 1
Can only use IF UPDATE within a CREATE TRIGGER statement.

Causes

The UPDATE() function returns a boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. The UPDATE() function is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

The syntax of the UPDATE() function is as follows:

UPDATE ( < column > )

The < column > parameter is the name of the column to test for either an INSERT or UPDATE action. Because the table name is specified in the ON the clause of the trigger, the table name need not be included before the column name.

Given that the UPDATE() function can only be used in a trigger, this error message will be encountered when it is used outside a trigger.

To illustrate on how this error can be generated, given the following table structures for the Products table and the table containing the product price updates, ProductPriceUpdate:

CREATE TABLE [dbo].[Products] (
    [ProductID]         INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [ProductName]       VARCHAR(50),
    [UnitPrice]         MONEY,
    [Quantity]          INT
) 
GO

CREATE TABLE [dbo].[ProductPriceUpdate] (
    [ProductID]         INT,
    [NewPrice]          MONEY
) 
GO

The following script tries to update the unit price in the Products table with the new prices defined in the ProductPriceUpdate table. If the UnitPrice was updated in the Products table, it then tries to delete those records from the ProductPriceUpdate table.

UPDATE [Prod]
SET [UnitPrice] = [Upd].[NewPrice]
FROM [dbo].[Products] [Prod] INNER JOIN [dbo].[ProductPriceUpdate] [Upd]
  ON Prod.[ProductID] = [Upd].[ProductID]

IF UPDATE([UnitPrice])
BEGIN
    DELETE [Upd]
    FROM [dbo].[ProductPriceUpdate] [Upd] INNER JOIN [dbo].[Products] [Prod]
      ON [Upd].[ProductID] = [Prod].[ProductID]
END 

Msg 140, Level 15, State 1, Line 6
Can only use IF UPDATE within a CREATE TRIGGER statement.

Solution / Work Around:

As mentioned earlier, the UPDATE() function can only be used within a trigger. If the purpose of using the UPDATE() function is to determine if any rows were updated in a table after an UPDATE statement, then the @@ROWCOUNT function can be used instead. The @@ROWCOUNT function returns the number of rows affected by the last statement.

Using the example earlier, this error can be avoided by replacing the IF UPDATE() statement with IF @@ROWCOUNT > 0, as shown in the following script:

UPDATE [Prod]
SET [UnitPrice] = [Upd].[NewPrice]
FROM [dbo].[Products] [Prod] INNER JOIN [dbo].[ProductPriceUpdate] [Upd]
  ON Prod.[ProductID] = [Upd].[ProductID]

IF @@ROWCOUNT > 0
BEGIN
    DELETE [Upd]
    FROM [dbo].[ProductPriceUpdate] [Upd] INNER JOIN [dbo].[Products] [Prod]
      ON [Upd].[ProductID] = [Prod].[ProductID]
END

If the DELETE statement will always be executed once the unit prices of the products have been updated, then another option is to create an UPDATE trigger for this purpose. In this UPDATE trigger, the UPDATE() function can now be used to determine if the UnitPrice column was updated and if it was updated, then the records in the ProductPriceUpdate table will be removed for those products that got updated.

CREATE TRIGGER [ProcessPriceUpdate] ON [dbo].[Products]
AFTER UPDATE
AS

IF UPDATE([UnitPrice])
BEGIN
    DELETE [Upd]
    FROM [dbo].[ProductPriceUpdate] [Upd] INNER JOIN [inserted] [Prod]
      ON [Upd].[ProductID] = [Prod].[ProductID]
END