Audit last Update in multiple Tables without Curso

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Audit last Update in multiple Tables without Curso

Author

Topic

gpc44
Starting Member

35 Posts

Posted - 2012-04-14 : 02:35:32

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?
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-14 : 10:33:34

You can run a query like this:

UPDATE a SET
	a.ModifiedDate = c.ModifiedDate
FROM
	tContact c
	INNER JOIN tAudit a
		ON a.pkPerson = t.pkPerson
WHERE
	t.ModifiedDate > a.ModifiedDate

If you have multiple tables you will need to run the query for each table.

If you need to do this only once a day, you can create a stored procedure with this code and run it at a scheduled time using SQL Server Agent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-15 : 15:05:31

your explanation and pseudo code seems to be contradicting for me

i updatet anything in my Table tContact, then ich have to write this Information in my tAuditTable Like this
the above sounds like you need to add data to tAuditTable but you're doing an update on tContat in statement below

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources