How to check rows for updates

Question

question

Fatherjack asked Feb 1, '10

How to check rows for updates

In an accident logging database we have a table of accidents and some of those relate to members of staff. When a new accident is recorded I have scheduled job to copy relevant data (12 columns) into the HR system. This is fine and dandy.

I now need to set up a step in the job to look for records in the accident database that have been updated since the last transfer of data and update those records in the HR system.

Should I;

A) add a column to the two tables in the two databases that can be set to the most recent update time and use that to select the rows for update?

B) add a bit column to the Accident table and set that to 1 when I want that row transferrred and set it back to 0 when ever the transfer has completed?

C) do some sort of check on each of the column values and update the data whereever I find its different?

D) something else ... ?

The HR record is not updated so this is one-way data flow from the Accident database table.

I think I know which answer I would prefer but getting your ideas may convince me otherwise.

Oh, no SSIS available and its not heavy duty enough to warrant replication - its maybe 20-30 accidents per month.

t-sql sql-agent cross-database data-transfer

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Feb 06, 2010 at 10:30 AM

I have cosen a solution using an AFTER UPDATE trigger to write out the PK column from the incident database into a table that I then use in a stored procedure with a JOIN to the incidents table to update the rows in the HR database and then truncate the log table. I would have added a datetime column to the log table if I thought the transactions were frequent enough that I would need to DELETE rather that TRUNCATE it after the process. Thanks to all.

0 ·

Answer 1 · 2010-02-01T06:50:44Z

Matt Whitfield answered Feb 1, '10

Given that it's 20-30 accidents per month, then I would do whatever you feel most comfortable with. Another option which you haven't covered is adding an update trigger and logging the records in a transfer table - but just an option to consider.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Feb 01, 2010 at 07:05 AM

mmm, liking that idea. I dont usually run to triggers for solutions but this could be a good one.

0 ·

Answer 2 · 2010-02-01T15:00:33Z

TimothyAWiseman answered Feb 1, '10

Matt is completely correct. Triggers are probably the best way to do it. There is no chance that new development will forget the logging portion of the process and the logging portion will occurr in the same transaction as the base update.

I have used transfer tables/log tables in a similar situation (as Matt suggests) but you can also consider simply having the trigger transfer it to the HR table directly.

If you do not want to use triggers, but use some sort of update-flag than for most other methods you will have to ensure that the update flag (whether that is a bit column, datetime, or another table entirely) is properly set every time an update happens. The best way to do this without triggers is to force all updates to go through a stored procedure that will always set the update flag. If you all ad-hoc updates or allow developers to write their own update procedures it is very possible that update-flag will be forgotten occassionally.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-02-02T07:57:20Z

Leo answered Feb 2, '10

I would create extra 2 columns in Accident Table Update_YN bit(0 by default) and Update_Date DateTime.

Update the those 2 fields everytime you have updated to other HR Database. You also have a records of Which are Updated and which are still outstanding to update by looking (0 and 1) and you can see when those are updated by date range (you might need it in the future to check how many accident are updated in a month or week or etc...).

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question