Update table x from table y when any column in y changes
Hi We have a table (x) containing staff information, that is re-created periodically from our HR system, using a stored procedure. We want to set up triggers that will act when values in this table change, so need to update it rather than re-create it. Suggestion is to create a second table (y) with the same information, then use the stored procedure to re-create y, and update table x by comparison with y. Thus, for example: - if a new record appears in y (new employee), then insert it into x; - if a record in x is not in y (leaver), then delete it from x; - if the content of any field in a record in y is different from x (eg employee changes grade or department), then update it in x. As I understand it, this should allow the changes in x to activate the trigger(s) we need. (The triggers will then activate further changes in another database, depending on what's changed in table x). I've been looking for solutions, and MERGE looks like a good option. However, the examples I've seen only update dependent on a change in a single field. Is it feasible to extend this to 'any change' in the records?
Thanks, all, for your replies. I'm sorry, I obviously didn't explain very well what I need. However, in trying to formulate a clearer explanation, I think I may have found an alternative approach that I'm going to try out. So thanks for your help. I'll log a new query if I still need help with my new approach.
Your question might point to a more complex requirement in the background tht I am not allowing for but in my opinion I would consider how you want to have the data in the two tables. (Just as a side not, you start describing that you data is in x and needs to go to y but then the bullet points seem to refer to them in the reverse scenario. maybe I am reading it wrongly?) Why not move the whole table from x to y? How often does the data in y need to be updated, or a better way to ask is How up to date must the data in y be? It may be a lot of data to move but if you can accept a daily refresh then moving the whole table is no big deal. I think there isnt enough information to make a complete recomendation here but having ALL the information might be too much for a forum type of question... Personally I would put quite a bit of effort into avoiding triggers.
Another option would be to consider introducing extended transactions through a program like [BizTalk]. That way you can commit changes to the table that you have, and also commit those same changes to another table, all as part of a single transaction that can be rolled forward or back but without blocking. :
OK - the script below is what I think will work. The objective is to populate an 'Event Log' table that records when there is a new staff record, or if an existing staff record is changed. Ref_StaffData is todays current staff listing, whilst Ref_StaffData_Backup is yesterday's listing - changes are identified by comparing the two. My original query was based on the idea that the Event Log would be populated by Insert and Update triggers on Ref_StaffData, but that wasn't working because the table emptied and repopulated every day, so all we'd get would be an insert trigger for every record in the table. I was trying to find a way around this. However, the script below avoids the need for triggers, by making the comparison and then updating Event Log directly. Btw - the staff table is only a couple of thousand records, with only a handful of changes on any given day, so efficiency isn't an issue. Thanks again for all your help. :-) -- Set up table variable to be used to populate IDM_event_log declare @changedStaff table (EmpNo int, ChangeType varchar(1)) -- Set up interim temp tables that will be used to populate the table variable select top 0 * into #NewMinusOld from Ref_StaffData select top 0 * into #OldMinusNew from Ref_StaffData -- Populate #NewMinusOld with records in Ref_StaffData that are different -- from those in yesterday's backup -- (Note, this will include new and changed records but won't distinguish -- between them) Insert into #NewMinusOld Select * from Ref_StaffData Except Select * from Ref_StaffData_Backup -- Populate #OldMinusNew with records in yesterday's backup that are different -- from those in today's Ref_StaffData. -- (Note, this will include changed and deleted records but won't distinguish -- between them) Insert into #OldMinusNew Select * from Ref_StaffData_Backup Except Select * from Ref_StaffData -- Populate @changedStaff with EmpNo, and ChangeType 'U' for any staff in -- #NewMinusOld, but not in #OldMinusNew - that is, any New staff record -- added since yesterday's backup. Insert into @changedStaff Select [Employee Number], 'N' from #NewMinusOld where [Employee Number] not in (Select [Employee Number] from #OldMinusNew) -- Populate @changedStaff with EmpNo, and ChangeType = 'U' for any staff -- in both #NewMinusOld and #OldMinusNew - that is, any staff record that -- has changed since yesterday's backup. Insert into @changedStaff Select [Employee Number], 'U' from #NewMinusOld where [Employee Number] in (Select [Employee Number] from #OldMinusNew) -- Now drop the interim temp tables, leaving the table variable to be used -- in the remaining stages of this procedure. drop table #NewMinusOld drop table #OldMinusNew -- Record the changes in the event log - this is the ultimate objective of the -- exercise Insert IDM_event_log (table_key, event_type, table_name) Select table_key = 'Emp_No = ' + cast(c.EmpNo as varchar(6)), CASE When c.ChangeType = 'U' then 6 When c.ChangeType = 'N' then 5 END as event_type, table_name = 'Ref_StaffData' From @changedStaff c Select * from dbo.IDM_event_log