| Author |
 Topic  |
|
|
zero1de
Yak Posting Veteran
Germany
66 Posts |
 Posted - 11/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 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
Yak Posting Veteran
Germany
66 Posts |
Posted - 11/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 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 |
Edited by - sunitabeck on 11/19/2012 15:10:30 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 11/19/2012 : 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
Yak Posting Veteran
Germany
66 Posts |
Posted - 11/20/2012 : 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
India
48064 Posts |
Posted - 11/20/2012 : 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
Yak Posting Veteran
Germany
66 Posts |
Posted - 11/21/2012 : 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
Yak Posting Veteran
Germany
66 Posts |
Posted - 11/22/2012 : 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
India
48064 Posts |
Posted - 11/25/2012 : 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/
|
|
|
| |
Topic |
|
Know when the value of a column has changed
