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)
8 Comments. Leave new
Good one.
good
I really appreciate all of your helpful blogs! You cover so many seemingly small items that actually make a HUGE difference.
Thank you!
Really Nice.Keep going
Thanks
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
Very good explanation. All this minute details make life so easy. Else it takes so much time to resolve minor issue like this.
very impressive
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…..