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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQLAuthority News – Active Directory Integration Sample Script
Next Post
SQLAuthority News – NASDAQ Uses SQL Server 2005 – Reducing Costs through Better Data Management

Related Posts

8 Comments. Leave new

  • Good one.

    Reply
  • good

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

    Thank you!

    Reply
  • Really Nice.Keep going

    Thanks

    Reply
  • 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

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

    Reply
  • very impressive

    Reply
  • Mujeeb Farooqi
    April 28, 2009 2:25 pm

    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…..

    Reply

Leave a Reply

Menu