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

avatar image

Bhuvans
220 19 19 24

(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

avatar image

WilliamD
26.2k 18 34 48

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

avatar image

Pavel Pawlowski
22.7k 10 15 26

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.

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:

x141

asked: Jul 27, 2011 at 02:00 AM

Seen: 1133 times

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

Copyright 2016 Redgate Software. Privacy Policy