This is my TabelCREATE 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)

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.

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.

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.

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 OptimizerTG

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.

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 OptimizerTG

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.

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 OptimizerTG

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.

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