trigger --get list of updated records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trigger --get list of updated records

Author

Topic

sqldoubt
Starting Member

17 Posts

Posted - 2010-06-02 : 18:00:56

there is a trigger that gets fired and sends a message when an update occurs. How to get the list of updated records using triggers?
If it is not supported, then how can i compare the old and new values of the records dynamically without having to hard code all column names.

Plzz help....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-02 : 18:04:35

The updated records are stored in the inserted trigger table; the old values are stored in the deleted trigger table. These trigger tables are only available in the trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqldoubt
Starting Member

17 Posts

Posted - 2010-06-02 : 18:14:51

SO shall i have to compare individual column names of old and new.

There is no other way to just dynamically get list of columns(values) that are updated??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-02 : 18:46:35

You can either test each column or find out which columns were updated via COLUMNS_UPDATED. Check out this section from CREATE TRIGGER topic in BOL:

quote:

Testing for UPDATE or INSERT Actions to Specific Columns
You can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 18:53:14

Dunno if helpful, but we have a routine in our Application that compares two column-sets. Given this SQL statement:


SELECT A.*, [__COMPARE__]='', B.*
FROM TableA AS A
     JOIN TableB AS B
        ON A.ID = B.ID

the routine looks for the specific column name "__COMPARE__" and compares following columns with preceding ones, thus does not need to know how many columns etc

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 18:54:30

"which columns were updated via COLUMNS_UPDATED"

My understanding is that only tells you that the column was included in the Update statement, not that the data has actually changed

sqldoubt
Starting Member

17 Posts

Posted - 2010-06-02 : 19:13:04

SO u mean column name starts with_COMPARE_??
and what do u mean by preceding ones.
is it the old and new value of same record?
cuz thats some thing i need to compare.

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 19:27:41

There is a dummy column, with the name "__COMPARE__" that separates the two sets of columns, and the routine compares the columns before this dummy column with the ones after it.

You can then use it for whatever you like, so for example if you stored the Before/After column values using a Trigger then my suggested routine would enable you to compare the column values in your application.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources