May 4, 2005 at 7:22 am
Hi
Today I've been tasked with something quite urgent and I'm more of an Admin person rather than programmer. I have an idea what I need to do but would be great to hear how other people have approached this. Unfortunately I cannot make use of Triggers in this situation.
Table A:
ClientNo, RespEmployer, LastModified + another 50 columns
Table B:
ClientNo, RespEmployer,Period.
Table A is part of an off shelf accounting package, when the RespEmployer column changes I need to be able to record this change in TableB(custom table) and also update the Period column with the current accounting period.
I plan to write a DTS package that runs nightly and extracts all data from Table A where LastModified date has changed, dump this data into a static temporary table. Then compare between Table A and Temp table and where the RespEmployer has changed in Table A then I update Table B with this new information and some further updates. Am I right in thinking that I need to write a cursor to do this or am I missing something very simple here?
Appreciate any feedback/suggestions...
May 4, 2005 at 8:03 am
You can use triggers
May 4, 2005 at 8:30 am
>>Unfortunately I cannot make use of Triggers in this situation.<<
You have to use either Triggers or third party tools
* Noel
May 5, 2005 at 1:50 am
If you can't use triggers and want to record all changes to Employer, you could first record current values of ClientNo/RespEmployer - for ALL clients - in the table B as a starting value. Then run a job nightly that would find differences in RespEmployer between the table A and table B, and record these changes to table B. You'd have to make sure that the value in table A is always compared with the NEWEST value in table B only. I would write a stored procedure that does all that and use the job to schedule it accordingly.
This would not require cursors, everything can be done with a set-based SQL... well, unless there is a hitch in the "and some further updates".
HTH, Vladan
May 5, 2005 at 9:49 am
Keep in mind that if you run this once a day you can't be sure to record all changes since if your data changes more than once in a day you will not know that. If this is supposed to provide an audit trail your once a day solution will not do that. If you are only attempting to keep a log of the most current change then you can do it something like this....
--update already existing records
Update Tableb
set Period = AccountingPeriod
from TableA a where Tableb.ClientNo = a.ClientNo and Tableb.RespEmployer = a.RespEmployer and
LastModified between date1 and date2
--insert records that don't exist
Insert into Tableb
select ClientNo, RespEmployer, Period from TableA a where LastModified between date1 and date2 and not exists (select 'not exists' from Tableb b where a.ClientNo = b.ClientNo and a.RespEmployer = b.RespEmployer)
hope this helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply