Archive

Posts Tagged ‘trigger’

Using Built-in system functions – UPDATE()

02.06.2011 2 comments

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