SQL SERVER – Difference Between UPDATE and UPDATE()

What is the difference between UPDATE and UPDATE()?

UPDATE is syntax used to update the database tables or database views.
USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO

UPDATE() is used in triggers to check update/insert to the database tables or database views.
Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
USE AdventureWorks ;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER
UPDATE
AS
IF
(
UPDATE (StateProvinceID)
OR
UPDATE (PostalCode) )
BEGIN
RAISERROR
(50009, 16, 10)
END;
GO

Please read additional details on BOL – UPDATE, BOL – UPDATE()

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Difference Between UPDATE and UPDATE()

  1. I really appreciate all of your helpful blogs! You cover so many seemingly small items that actually make a HUGE difference.

    Thank you!

  2. Really very helpfull for keep maintaining the issues,informations.outer side these looks normal but whn we think it deeply then really matters lots of

  3. Very good explanation. All this minute details make life so easy. Else it takes so much time to resolve minor issue like this.

  4. I wrote a trigger but its not updating the record. Query is working fine..
    Can you plz let me know where im wrong….

    Alter TRIGGER Trig_FOW
    ON dbo.tbl_FOW
    FOR INSERT, UPDATE, DELETE
    AS
    IF (SELECT COUNT(*) FROM inserted) > 0
    BEGIN
    if (Select count(*) from CAG.dbo.tbl_FOW
    Where
    CAG.dbo.tbl_FOW.Fall_No=Fall_No AND
    CAG.dbo.tbl_FOW.MatchTeam_ID= MatchTeam_ID AND
    CAG.dbo.tbl_FOW.Innings=Innings AND
    CAG.dbo.tbl_FOW.Player_ID=Player_ID) > 0
    BEGIN
    Update CAG.dbo.tbl_FOW
    SET
    CAG.dbo.tbl_FOW.Fall_on_Runs=Fall_on_Runs,
    CAG.dbo.tbl_FOW.Fall_on_Balls=Fall_on_Balls,
    Where
    CAG.dbo.tbl_FOW.Fall_No=Fall_No AND
    CAG.dbo.tbl_FOW.MatchTeam_ID=MatchTeam_ID AND
    CAG.dbo.tbl_FOW.Innings=Innings AND
    CAG.dbo.tbl_FOW.Player_ID=Player_ID
    END
    else
    BEGIN
    INSERT CAG.dbo.tbl_FOW SELECT * FROM inserted
    END
    END

    —————————————————————————

    Secondly, i also wanted to know that when i try to use the sqlconnection begin and commit transaction from asp.net code… other application that is using the same db hangs untill I commit the transaction…

    Is there any way to that i im missing…
    I was not sure for this solution so i created a new db and try to insert/update or delete record from the main db using triggers…..

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s