April 14, 2012 at 12:39 am
I want to update the field "ModifiedDate" in an audit table with the last (up) date (ModifiedDate) from multiple tables. Goal: When was at a table with data last updated person something. PrimaryKey = PersonID. Is it possible to achieve this without a cursor?... maybe with cte ???
Example: If i have 2 Tables (tPerson, tContact), and i updatet anything in my Table tContact, then ich have to write this Information in my tAuditTable Like this (pseudoCoide 😉
if tContact.ModifiedDate > tAudit.ModifiedDate
then Update tContat SET ModifiedDate = tAudit.ModifiedDate
Where tContact.pkPerson = tAudit.pkPerson
This processing have to run one time a day - for each tables and each records.
many thanks
Nicole
:kiss:
April 14, 2012 at 3:25 am
It depends. To help us to help you it would be best to ask yourself and give us the answers to the following questions:
1) do you want this run on a single table, a selected set of tables or all user tables in a database?
2) do all tables that you wish to include all have a current modifieddate field in?
3) do all the primary keys from the tables have the same datatype (ie int)?
4) how do you want to deal with the case of rows being deleted from the source tables (a- delete the relevent row from the audit, or b- treat as an update when the deleted row cannot be matched)?
5) do you want the results in a single table, or multiple audit tables (one per source table)?
If any of the questions above are "I don't know" then a design decision must be made by you before we can help.
Fitz
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply