Know when the value of a column has changed

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

Know when the value of a column has changed

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 13:51:38

Hello,

I want to know, when a column value every time changes in a table.

This is my Tabel
CREATE TABLE [dbo]. [Table_NEW] (
[ID] int NOT NULL,
[DWMODDATETIME] datetime NULL,
[DWMODUSER] nvarchar (20) NULL,
..
Every time the value of DWModdatetime is changed, I want to know. The values are important for another procedure, which then picks up only the changed records. How can I solve the problem (trigger reluctantly)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 13:57:15

That does sound like a case where you would need to use a trigger. None of the other alternatives are synchronous or reliable.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 14:25:24

I would like to work with a trigger but I am a beginner in MS SQL environment. Would you have an example of me ?

THX

quote:
Originally posted by sunitabeck

That does sound like a case where you would need to use a trigger. None of the other alternatives are synchronous or reliable.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 14:34:54

Take a look at the MSDN page here - the documentation is pretty good, and they have examples as well. In your case, it would be something like shown below:

CREATE TRIGGER dbo.Table_NEW_Trigger
ON dbo.Table_New
FOR UPDATE, INSERT
AS

-- you have access to two virtual tables - INSERTED and DELETED.
-- DELETED contains all the affected rows as they existed before an update operation
-- or before a delete operation.
-- INSERTED contains all the affected rows as they exist after the update operation
-- or after the insert operation.
-- You can select from those tables to do what you need to do.

-- an example: simply selecting the affected records from INSERTED.
SELECT * FROM INSERTED;

GO

Play with it in a test environmen to see if it behaves as you expect, and if you run into roadblocks, post the code and I am sure people on the forum would be able to help.

edit: sorry forgot to include the MSDN link: http://msdn.microsoft.com/en-us/library/ms189799.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-19 : 16:31:04

Not sure what your objective is but if the following assumptions are true:
- you just want to know the datetime of the lastest change for the row (or specific column value)
- you only write to the table an application or Stored procedure (no adhoc access)

Then it may be as simple as adding a column: [LastModifed] datetime not null default(getdate())

However, if you need a log of all historical values, who changed each version, etc then you need a trigger writing to an audit table.

Be One with the Optimizer
TG

zero1de
Posting Yak Master

105 Posts

Posted - 2012-11-20 : 02:48:30

Hi,
there will be a program to grab the records that have been changed. The field DWModDatetime is already been updated over the main program with the final changes. I want to commit only the changed rows every 1h to an other procedure without change anything on Field DWddatetime. Becouse this Field will be controled over main software.

Trigger is a good way but I do not know how I pass the values DWID and DWModdatetime to these second prozedure or program.

THX

quote:
Originally posted by TG

Not sure what your objective is but if the following assumptions are true:
- you just want to know the datetime of the lastest change for the row (or specific column value)
- you only write to the table an application or Stored procedure (no adhoc access)

Then it may be as simple as adding a column: [LastModifed] datetime not null default(getdate())

However, if you need a log of all historical values, who changed each version, etc then you need a trigger writing to an audit table.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 03:40:18

quote:
Originally posted by zero1de

Hi,
there will be a program to grab the records that have been changed. The field DWModDatetime is already been updated over the main program with the final changes. I want to commit only the changed rows every 1h to an other procedure without change anything on Field DWddatetime. Becouse this Field will be controled over main software.

Trigger is a good way but I do not know how I pass the values DWID and DWModdatetime to these second prozedure or program.

THX

quote:
Originally posted by TG

Not sure what your objective is but if the following assumptions are true:
- you just want to know the datetime of the lastest change for the row (or specific column value)
- you only write to the table an application or Stored procedure (no adhoc access)

Then it may be as simple as adding a column: [LastModifed] datetime not null default(getdate())

However, if you need a log of all historical values, who changed each version, etc then you need a trigger writing to an audit table.

Be One with the Optimizer
TG

if you want to do it every 1 h then you can go for asynchronous methods as well

Create a sql agent job to do auditing logic. You can have trigger log changes record info to a audit table with timestamp. Then in sql job look for changes in audit table since last run and pick up delta and do your required processing.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-11-21 : 02:40:31

Sounds good but I have no idea how to do that? I've seen the SQL job but as I said do not know how? Can you help me.

THX

quote:
Originally posted by visakh16

quote:
Originally posted by zero1de

Hi,
there will be a program to grab the records that have been changed. The field DWModDatetime is already been updated over the main program with the final changes. I want to commit only the changed rows every 1h to an other procedure without change anything on Field DWddatetime. Becouse this Field will be controled over main software.

Trigger is a good way but I do not know how I pass the values DWID and DWModdatetime to these second prozedure or program.

THX

quote:
Originally posted by TG

Not sure what your objective is but if the following assumptions are true:
- you just want to know the datetime of the lastest change for the row (or specific column value)
- you only write to the table an application or Stored procedure (no adhoc access)

Then it may be as simple as adding a column: [LastModifed] datetime not null default(getdate())

However, if you need a log of all historical values, who changed each version, etc then you need a trigger writing to an audit table.

Be One with the Optimizer
TG

if you want to do it every 1 h then you can go for asynchronous methods as well

Create a sql agent job to do auditing logic. You can have trigger log changes record info to a audit table with timestamp. Then in sql job look for changes in audit table since last run and pick up delta and do your required processing.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-11-22 : 11:04:34

can I have looked at, but as far as I could see is only the SQL statments for? This brings me nothing becouse I need the delta of the rows!

THX

quote:
Originally posted by visakh16

quote:
Originally posted by zero1de

Hi,
there will be a program to grab the records that have been changed. The field DWModDatetime is already been updated over the main program with the final changes. I want to commit only the changed rows every 1h to an other procedure without change anything on Field DWddatetime. Becouse this Field will be controled over main software.

Trigger is a good way but I do not know how I pass the values DWID and DWModdatetime to these second prozedure or program.

THX

quote:
Originally posted by TG

Not sure what your objective is but if the following assumptions are true:
- you just want to know the datetime of the lastest change for the row (or specific column value)
- you only write to the table an application or Stored procedure (no adhoc access)

Then it may be as simple as adding a column: [LastModifed] datetime not null default(getdate())

However, if you need a log of all historical values, who changed each version, etc then you need a trigger writing to an audit table.

Be One with the Optimizer
TG

if you want to do it every 1 h then you can go for asynchronous methods as well

Create a sql agent job to do auditing logic. You can have trigger log changes record info to a audit table with timestamp. Then in sql job look for changes in audit table since last run and pick up delta and do your required processing.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-25 : 09:30:10

First of all you need an audit table to log the rows which have been processed each time. Have a datetime column in table to indicate till what records were processed in a particular hour. this will help you to identify the deltas for subsequent hours. in sql job,call a procedure which retrieves all records from table after last processed time (you may keep a control table for storing this).then do required logic over those batch of rows. in end log the maximum datetime value of rows as last processed date value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources