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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Know when the value of a column has changed
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Posting Yak Master

Germany
105 Posts

Posted - 11/19/2012 :  13:51:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  13:57:15  Show Profile  Reply with Quote
That does sound like a case where you would need to use a trigger. None of the other alternatives are synchronous or reliable.
Go to Top of Page

zero1de
Posting Yak Master

Germany
105 Posts

Posted - 11/19/2012 :  14:25:24  Show Profile  Reply with Quote
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.

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  14:34:54  Show Profile  Reply with Quote
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

Edited by - sunitabeck on 11/19/2012 15:10:30
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 11/19/2012 :  16:31:04  Show Profile  Reply with Quote
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
Go to Top of Page

zero1de
Posting Yak Master

Germany
105 Posts

Posted - 11/20/2012 :  02:48:30  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/20/2012 :  03:40:18  Show Profile  Reply with Quote
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/

Go to Top of Page

zero1de
Posting Yak Master

Germany
105 Posts

Posted - 11/21/2012 :  02:40:31  Show Profile  Reply with Quote
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/



Go to Top of Page

zero1de
Posting Yak Master

Germany
105 Posts

Posted - 11/22/2012 :  11:04:34  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/25/2012 :  09:30:10  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000