InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 05:50:02 GMT20http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]Jason-299789 (10/9/2012)[/b][hr][quote][b]Gazareth (10/9/2012)[/b][hr][quote][b]Jason-299789 (10/9/2012)[/b][hr][quote][b]Gazareth (10/9/2012)[/b][hr][quote][b]terry999 (10/2/2012)[/b][hr]illegal?[/quote]Hmm, yeah. Was wondering that myself![/quote]I think that Joe was probably refering to some form of regulatory requirement, eg Sarbanes Oxley/solvency II or other federal requirement to maintain data change history.Who knows.....[/quote]Ah yes. Luckily we're not all subject to American law... :-D[/quote]If you work in the finance industry in Europe we do have Solvency II regulations that have to be in place for 2013/14 (I believe) and one of those requirements is full audit trails of data changes, including data traceability through ETL into Data Warehouse's, especially if they are used as a source for regulatory reporting to bodies like the FSA, Lloyds etc.[/quote]Fair enough :-)Tue, 09 Oct 2012 04:30:55 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]Gazareth (10/9/2012)[/b][hr][quote][b]Jason-299789 (10/9/2012)[/b][hr][quote][b]Gazareth (10/9/2012)[/b][hr][quote][b]terry999 (10/2/2012)[/b][hr]illegal?[/quote]Hmm, yeah. Was wondering that myself![/quote]I think that Joe was probably refering to some form of regulatory requirement, eg Sarbanes Oxley/solvency II or other federal requirement to maintain data change history.Who knows.....[/quote]Ah yes. Luckily we're not all subject to American law... :-D[/quote]If you work in the finance industry in Europe we do have Solvency II regulations that have to be in place for 2013/14 (I believe) and one of those requirements is full audit trails of data changes, including data traceability through ETL into Data Warehouse's, especially if they are used as a source for regulatory reporting to bodies like the FSA, Lloyds etc.Tue, 09 Oct 2012 04:16:01 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]Jason-299789 (10/9/2012)[/b][hr][quote][b]Gazareth (10/9/2012)[/b][hr][quote][b]terry999 (10/2/2012)[/b][hr]illegal?[/quote]Hmm, yeah. Was wondering that myself![/quote]I think that Joe was probably refering to some form of regulatory requirement, eg Sarbanes Oxley/solvency II or other federal requirement to maintain data change history.Who knows.....[/quote]Ah yes. Luckily we're not all subject to American law... :-DTue, 09 Oct 2012 04:11:02 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]Gazareth (10/9/2012)[/b][hr][quote][b]terry999 (10/2/2012)[/b][hr]illegal?[/quote]Hmm, yeah. Was wondering that myself![/quote]I think that Joe was probably refering to some form of regulatory requirement, eg Sarbanes Oxley/solvency II or other federal requirement to maintain data change history.Who knows.....Tue, 09 Oct 2012 03:54:08 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]terry999 (10/2/2012)[/b][hr]illegal?[/quote]Hmm, yeah. Was wondering that myself!Tue, 09 Oct 2012 03:29:07 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxGood point, I'd forgotten thatTue, 09 Oct 2012 01:55:31 GMTterry999http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]terry999 (10/2/2012)[/b][hr]Thanks Jason I had made that change.From now on I will use MERGE to replace Triggers for audit - all in one place.[/quote]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?Wed, 03 Oct 2012 08:07:30 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxillegal?Tue, 02 Oct 2012 12:23:56 GMTterry999http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxThanks Jason I had made that change.From now on I will use MERGE to replace Triggers for audit - all in one place.Tue, 02 Oct 2012 12:23:17 GMTterry999http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxLook at audit tools that will capture all the data and not be illegal.Tue, 02 Oct 2012 07:37:42 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspx[quote][b]terry999 (10/2/2012)[/b][hr]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[/quote]Sorry theres a coding error I believe it should read[code="sql"]MERGE tblCustomer AS targetUSING (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.countryWHEN 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 [/code]Tue, 02 Oct 2012 06:55:37 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxThank 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.nameTue, 02 Oct 2012 06:47:01 GMTterry999http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxIf you are using the Enterprise edition of SS2K8, then [url=http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx]change data capture [/url]is well worth a look.Tue, 02 Oct 2012 05:12:15 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxYou 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[code="sql"]MERGE tblCustomer AS targetUSING (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.countryWHEN 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 tChangesINTO #tAudit WHERE chkNew <> ChkOld[/code]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.Tue, 02 Oct 2012 04:54:44 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1366942-392-1.aspxHi 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.[code="other"]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=2insert into #tAuditSELECT Actiontype, AuditDate, name, country FROM(MERGE tblCustomer AS targetUSING (SELECT * FROM tblUpdates ) AS source ON (target.cid = source.cid)WHEN MATCHED THEN UPDATE SET name = source.name, country = source.countryWHEN 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 tChangesWHERE chkNew <> ChkOldselect * from #tAuditdrop table #tAuditdrop table tblCustomerdrop table tblUpdates[/code]Is there a better way to achive this.Tue, 02 Oct 2012 04:30:33 GMTterry999