SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
66 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
Yak Posting Veteran

Germany
66 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
5914 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
Yak Posting Veteran

Germany
66 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
52249 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
Yak Posting Veteran

Germany
66 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
Yak Posting Veteran

Germany
66 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
52249 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000