x

Update Trigger Doubt?

If we create an Update Trigger on a particular column, can we get the value which row has updated?

CREATE TRIGGER BRTEST
ON WisePoints
AFTER UPDATE 
AS 
IF ( UPDATE (TotalPoints))
BEGIN
END;
How to get which row has updated in the table WisePoints?
more ▼

asked Jul 27, 2011 at 02:00 AM in Default

Bhuvans gravatar image

Bhuvans
220 19 19 21

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If you want to return the updated information, you simply need to access the internal tables of the trigger:

CREATE TRIGGER BRTEST
ON WisePoints
AFTER UPDATE 
AS 
IF ( UPDATE (TotalPoints))
BEGIN
  SELECT TotalPoints as UpdatedTotalPoints 
  FROM inserted -- this is the list of new values that you just updated
END;

Be aware that this will return more than one row if more than one row is manipulated at once - e.g.

UPDATE WisePoints
SET TotalsPoints = TotalPoint + 10  -- add ten points to all records in the table
This will fire the trigger once, returning a result set of all new TotalPoints for the records in the table.
more ▼

answered Jul 27, 2011 at 02:57 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Thank You William
Jul 27, 2011 at 03:48 AM Bhuvans
(comments are locked)
10|1200 characters needed characters left

Inside triggers there are available inserted and deleted virtual tables.

For the AFTER UPDATE trigger the inserted virtual table contains all affected rows with new values. The deleted virtual table contain all affected rows with original values.

The triggers on MS SQL Server are fired once for all the affected records an you have a set of the affected records available in the virtual tables.
more ▼

answered Jul 27, 2011 at 02:24 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Had my answer ready to post and a phone call got in the way
Jul 27, 2011 at 02:57 AM WilliamD
Thank you Pavel
Jul 27, 2011 at 03:48 AM Bhuvans
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x121

asked: Jul 27, 2011 at 02:00 AM

Seen: 973 times

Last Updated: Jul 27, 2011 at 02:03 AM