Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using MERGE for Audting


Using MERGE for Audting

Author
Message
terry999
terry999
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 677
Hi
I'm looking at using Merge which I've never used. I've achieved Auditing before with an After trigger that writes the old row to the audit table. I'm only interested in writting to the audit table if the values change so use checksum.


create table tblCustomer
(CID int not null primary key,
name varchar(10) not null,
country varchar(15) not null)

insert into tblCustomer values (1, 'Bob', 'UK'), (2, 'Claire', 'USA'), (3,'Terry','France')
create table tblUpdates
(
CID int not null,
name varchar(10) not null,
country varchar(15) not null
)

insert into tblUpdates values (1, 'Bob', 'UK'), (2, 'Claire', 'Mexico'), (3,'Terrance','Spain'),(4,'Abdul','Egypt')

select 'UPDATE' as actiontype, getdate() as AuditDate, name, country
into #tAudit from tblCustomer where 1=2

insert into #tAudit
SELECT Actiontype, AuditDate, name, country FROM
(
MERGE tblCustomer AS target
USING (SELECT * FROM tblUpdates ) AS source
ON (target.cid = source.cid)

WHEN MATCHED
THEN UPDATE SET name = source.name, country = source.country
WHEN NOT matched
   THEN INSERT
   VALUES (CID,Name, Country)

OUTPUT $action as Actiontype, getdate() as AuditDate,
CASE WHEN $action = 'INSERT' THEN inserted.name ELSE deleted.name END as name,
CASE WHEN $action = 'INSERT' THEN inserted.country ELSE deleted.country END as country,
binary_checksum(inserted.name, inserted.country) as chkNew,
binary_checksum(deleted.name, deleted.country) as ChkOld) as tChanges
WHERE chkNew <> ChkOld

select * from #tAudit

drop table #tAudit
drop table tblCustomer
drop table tblUpdates



Is there a better way to achive this.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
You can use the OUTPUT to insert rows into a table rather than the Outer Select, which is rather cumbersome.

this might be better, and you'd need to double check the code works but theres no reason it shouldnt



MERGE tblCustomer AS target
USING (SELECT * FROM tblUpdates ) AS source
ON (target.cid = source.cid)

WHEN MATCHED
   AND (target.name!=source.name
      or target.country!=source.country)
THEN UPDATE SET name = source.name, country = source.country
WHEN NOT matched
   THEN INSERT
   VALUES (CID,Name, Country)

OUTPUT $action
   , getdate()
   , CASE WHEN $action = 'INSERT' THEN inserted.name ELSE deleted.name END as name
   , CASE WHEN $action = 'INSERT' THEN inserted.country ELSE deleted.country END as country
   , binary_checksum(inserted.name, inserted.country) as chkNew
   ,binary_checksum(deleted.name, deleted.country) as ChkOld) as tChanges
INTO #tAudit          
WHERE chkNew <> ChkOld



Edit I've just seen that you dont have a clause to update only the rows that have changed, so I've added it to the code this will then only update changed rows and insert new rows.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8935 Visits: 19009
If you are using the Enterprise edition of SS2K8, then change data capture is well worth a look.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
terry999
terry999
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 677
Thank you. Your example is easier to read.

BTW I was restricting to only chnage rows by comparing the checksum values.

I didn't know you could add additional filters after is matched i.e. target.name != source.name
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
terry999 (10/2/2012)
Thank you. Your example is easier to read.

BTW I was restricting to only chnage rows by comparing the checksum values.

I didn't know you could add additional filters after is matched i.e. target.name != source.name



Sorry theres a coding error I believe it should read


MERGE tblCustomer AS target
USING (SELECT * FROM tblUpdates ) AS source
ON (target.cid = source.cid)

WHEN MATCHED
   AND (target.name!=source.name
      or target.country!=source.country)
THEN UPDATE SET name = source.name, country = source.country
WHEN NOT matched
   THEN INSERT
   VALUES (CID,Name, Country)

OUTPUT $action
   , getdate()
   , CASE WHEN $action = 'INSERT' THEN inserted.name ELSE deleted.name END
   , CASE WHEN $action = 'INSERT' THEN inserted.country ELSE deleted.country END
   , binary_checksum(inserted.name, inserted.country)
   ,binary_checksum(deleted.name, deleted.country)
INTO #tAudit          



_________________________________________________________________________
SSC Guide to Posting and Best Practices
terry999
terry999
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 677
Thanks Jason

I had made that change.

From now on I will use MERGE to replace Triggers for audit - all in one place.
terry999
terry999
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 677
illegal?
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8297
terry999 (10/2/2012)
Thanks Jason

I had made that change.

From now on I will use MERGE to replace Triggers for audit - all in one place.



Note that this places the onus on the developer to code the audit stuff for EVERY SINGLE DML OPERATION ON EACH TABLE. A trigger can be written once and will capture ALL DML operations, regardless of provenance. Even if your devs are rigorous, what about that quick "oopsie" data cleanup operation you need to do because someone screwed something up. Will you ALWAYS remember to do the MERGE, even for ad hoc DML?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
terry999
terry999
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 677
Good point, I'd forgotten that
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search