Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Built-in system functions – UPDATE()

Using Built-in system functions – UPDATE()


UPDATE() function can be used to determine whether an INSERT or UPDATE was made on the specified column.

You can use it inside a trigger to test if a column has been updated by INSERT/UPDATE statement.

for example, I have below table:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE SqlAndMe

 

CREATE TABLE dbo.Salary

(

      ID          INT,

      Name        VARCHAR(50),

      Salary      MONEY

)

And I have also created on the table for an update as below:

CREATE TRIGGER Updated_Salary

ON dbo.Salary

AFTER UPDATE

AS

IF ( UPDATE (Salary) )

BEGIN

ROLLBACK

END

 

INSERT INTO dbo.Salary VALUES (1, 'Vishal', 1000)

Now if someone tries to update the Salary column, a message will be displayed as below:

UPDATE      dbo.Salary SET Salary = 2000

WHERE       ID = 1

Result Set:

Msg 3609, Level 16, State 1, Line 1

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

However, other columns can be updated without any issues.

UPDATE      dbo.Salary SET Name = 'Gajjar'

WHERE       ID = 1

Result Set:

 

(1 row(s) affected)

Hope This Helps!

Vishal

  1. Tom Groszko
    03.06.2011 12:10 | #1

    I don’t think this function tells you that a column was updated, it only tells you that the column was used in an update statement, the column may have been set to it’s original value and not changed.

  2. Vishal
    03.06.2011 12:48 | #2

    indeed, you can use compare the values in INSERTED and DELETED tables to check if it has been set to original value.

  1. No trackbacks yet.