April 1, 2011 at 6:00 pm
Hi,
I got two tables
when 100 value in a column of table 1 is updated to 200.. this 100 shld be entered into table 2
and if agian i update 200 in table1 to 300 then that 200 should be entered to the table 2
April 2, 2011 at 12:17 am
There are a variety of methods to do this. You can add update code to your front end app, to a stored procedure, a trigger, etc.
Likely, a trigger will work in this example. Basically, you can update table2 based on table 1's values before table1 is updated. Don't use the inserted table in this case because it will contain the NEW values, but you want the pre-update values, so update from table1 before update.
Also, ensure your logic deals with the 1st update to table1. There will be no matching rows in table2 to update [yet]. Therefore, you'll need to insert the rows with the orig values before the initial update, or check to ensure the rows exist and insert if not exists.
Also, with lots of updates like this, and saving off entire rows, the table will grow pretty big. You may want to find a way to save only the column changed if it was updated, and it's old value rather than save the entire row.
I used a bunch of key words here so you can dig into books online for details of how to implement this.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 2, 2011 at 1:31 am
I think, you can also try OUTPUT clause for this problem. Check the below link whether it is matching your requirement.
http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 5, 2011 at 10:29 am
Try this:
create table A (ID int identity(1,1), col1 int);
insert into A (col1) select 100 union select 200 union select 300;
create table A_audit (A_ID int, col1 int, date_changed datetime);
go
create trigger A_changes on A for update as
insert into A_audit (A_ID, col1, date_changed)
select d.ID, d.col1, getdate()
from inserted i
inner join deleted d on i.ID = d.ID and i.col1 <> d.col1;
go
select * from A
select * from A_audit
update A set col1 = col1 + 1
drop table A
drop table A_audit
If there is anything you don't understand about this, please ask.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 5, 2011 at 1:37 pm
Hi,
There are many ways you can have history of your data,since you are using SQL 2008 there are new features are there where you can use them depending upon your need.
a.)Change Tracking
b.)Change Data Capture
These features are included in SQL 2008 for tracking the changes from current state to upcoming state(UPDATE)
To determine data changes, application developers had to implement a custom tracking method in their applications by using a combination of triggers, timestamp columns, and additional tables. Creating these applications usually involved a lot of work to implement, led to schema updates, and often carried a high performance overhead.
Thanks
Parthi
April 5, 2011 at 2:00 pm
parthi-1705 (4/5/2011)
a.)Change Trackingb.)Change Data Capture
These features are included in SQL 2008...
Yes, this I'd another way. CDC is Enterprise only if I remember correctly. Also, the tables can get quite large, but there is also a nice history retention threshold that you can adjust too. And, all of the CDC data is stored in system tables which you can query, archive, etc.
I still seem to prefer my own table/trigger, especially if only tracking a table or two. I prefer to see if a field was UPDATE()'d and save off name/before/after/who/when.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply