Tracking Deleted Records (record deletes)

Mikhail Zolikoff

Background

• We need to track deleted records across various tables from an external system via the legacy SOAP API.
• We don't necessarily want to add delete auditing to tables, as we only need to track one item (deletes) and we don't want to have largish tables hanging around tracking everything our users do. Additionally, audit tables aren't directly accessible via the API.
• We're considering creating a Deletes table, then putting triggers on the various tables and storing “record deletes” in this table.
• We're also considering created an IsDeleted boolean field on each table, then having this field set to true using an On Delete Trigger.

Questions

• Has anyone had a need for this in the past?
• Has anyone solved this in a different or better way?

Benjamin Seitz

@Mikhail Zolikoff
Do you not want to turn on auditing because you don't want to have table updates logged? If that's the case I'd consider turning on auditing, and disable the Audit Update trigger on the tables you want to log deletes.

Aaron Thompson

@Mikhail Zolikoff I think your idea sounds viable. It has the advantage of not altering the way CRM is intended to behave viz a viz audit tables. You can then add any custom logic to your triggers so that it tracks exactly the table changes that you want.

Mikhail Zolikoff

@Benjamin Seitz I think because audits track everything about a table (which we don't want to do), and as such start taking up space. The MVP of this need is:

1. Was a record deleted?
2. If so, which one?

Audits seem like overkill for this, unless you know of a way (or you stated it and I'm not understanding what you wrote) to use auditing to track this but have it be incredibly lightweight.

Mikhail Zolikoff

@Aaron Thompson Per my reply to @Benjamin Seitz, the MVP of this need is:

1. Was a record deleted?
2. If so, which one?

Is a new table with triggers the smallest and easiest solution to this, or something else, like some low level “track deletes only” auditing?

Michael Flynt

@Mikhail Zolikoff Blackbaud has already solved this problem for BBDW ETL. Their bbetlaudit schema contains audit tables (3 columns: ID, DATE, AUDITTYPECODE), which are populated by on Delete triggers, of form dbo.TR_<baseTable>_AUDIT_ETLDELETEDID. These may exist for some tables of interest already. You could readily apply the technique for novel tables.

Mikhail Zolikoff

@Benjamin Seitz Additionally, as we understand it, audit tables aren't accessible via the API, which is another requirement, as we need to be able to determine from an external system whether a record has been deleted or not.

Mikhail Zolikoff

@Michael Flynt Is the BBDW accessible via API and a proxy user?

Michael Flynt

@Mikhail Zolikoff I was not suggesting the BBDW be any part of this. I was just pointing out that the mechanics inside OLTP to support BBDW ETL exist for exactly the purpose you seek. You'd need to return data though something that is automatically exposed to the API. DataList, SimpleDataList, ViewForm are all options. Permissions are better set on the Lists. I'd suggest a datalist using some kind of parameter for table name (needs to be constrained, perhaps tablecatalog,ID for parent table), then you'll want at least one date fields so you don't retrieve the universe every call. You'll have to set permissions appropriately for the Proxy User. It might require adding permissions on the underlying tables. You do not want to mess around with the BBDW itself for this, for obvious reasons.

Mikhail Zolikoff

@Michael Flynt A fantastic idea…

• How would your proposed data list get data, via new Delete Triggers?
• Or can / should any existing delete trigger be modified? (Although I don't think I would want to muck with an existing trigger and business process)
• And then I presume that new Delete Triggers would have to be created on every table this data list needs to track (ugh)
  

Mikhail Zolikoff

@Benjamin Seitz Oh, I understand now - there are two different types of audit triggers: UPDATE and DELETE. Disable UPDATE, enable DELETE.

But then what we're now encountering is that audit table information isn't accessible via the API… but I'm wondering if audit table information can be fed to a data list, like what @Michael Flynt is suggesting.

Benjamin Seitz

@Mikhail Zolikoff
I would personally make a custom datalist to pull the information you want with some dynamic SQL. However, out of the box, there is a built in SSRS report that can pull audit table info which you could technically execute and read via the API. You can see that in the front end via Administration → Audit Tables → All audit reports (task in the top left)

Mikhail Zolikoff

@Benjamin Seitz

My apologies for the basic newbie questions:

1. How would I access this report via the SOAP API?
2. How does one populate a data list via SQL?
   • The front end GUI only allows one source view, yet we want this table to be populated with delete information from multiple tables, so the GUI doesn't seem like the way to go.
   • Plus, it doesn't seem like the GUI has access to the deeper information that we need.
   • Is it a List Builder that we need vs. a Data List? EDIT: ah, that isn't what List Builder does.
     

Benjamin Seitz

@Mikhail Zolikoff

To call a report via the SOAP API, look for the ReportsExport in the AppFxWebService operations list.

For a data list, you would need to create a custom data list and write your own SQL to pull together the audit tables. Here's the SDK reference documentation: https://webfiles-sc1.blackbaud.com/files/support/helpfiles/infinitydevguide/content/sdk/infinitydatalists/welcomeinfinitydatalists.html?tocpath=Data%20Lists%7C_____0

Mikhail Zolikoff

@Benjamin Seitz I was able to get back data from this report using our proxy user, but:

1. Our proxy user had to be given elevated rights
2. The results came back in base64, so this has to be decoded
3. The “main” report ("Table Audit Report") doesn't include the GUID of the record deleted. For this, you have to then query the “Audit Change Details” report, which we are still figuring out.

For anyone else reading this thread, the Postman body for testing the main report is:

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<ReportsExportRequest xmlns="Blackbaud.AppFx.WebService.API.1">
<ClientAppInfo REDatabaseToUse="DBID" ClientAppName="whatever_you_want" TimeOutSeconds="300" />
<ReportId>GUID of the Recent Changes audit report</ReportId
<ExportType>csv</ExportType>
<ParameterValues>
<Values xmlns="bb_appfx_dataforms">
<fv ID="TABLENAME_LIST">
<Value xsi:type="xsd:string">All Tables</Value>
</fv>
<fv ID="DATE_START">
<Value xsi:type="xsd:string">04/25/2025</Value>
</fv>
<fv ID="INCLUDE_INSERTS">
<Value xsi:type="xsd:string">false</Value>
</fv>
<fv ID="INCLUDE_UPDATES">
<Value xsi:type="xsd:string">false</Value>
</fv>
<fv ID="INCLUDE_DELETES">
<Value xsi:type="xsd:string">true</Value>
</fv>
</Values>
</ParameterValues>
</ReportsExportRequest>
</soap12:Body>
</soap12:Envelope>