In MSSQL 2019, I have a complicated stored procedure that performs the equivalent of a cascaded soft-delete and then a cascaded hard-delete (inside a single transaction). This sp is called inside my custom Delete service method, as shown below, and works correctly.
public DeleteResponse Delete(DeletePortfolio request)
{
using var db = AutoQuery!.GetDb<Portfolio>(base.Request);
var accountIdStr = base.Request.GetSession().GetUserAuthIdOrName();
var accountId = GeneralHelper.TryParseGuid(accountIdStr);
using (var cmd = db.SqlProc("Portfolio.Usp_Portfolio_Cascade_Delete"))
{
cmd.AddParam("PortfolioId", request.Id);
cmd.AddParam("By", accountId);
cmd.ExecuteNonQuery();
}
return new DeleteResponse();
}
I have the problem that there is no record of the Delete being called in the ServiceStack AutoQuery Audit table.
Other Delete calls are being audited, but those services do not call an sp.
If DeletePortfolio is the AutoQuery implementation you’re referring to? you’ve overridden its behavior by providing a custom implementation. You could call AutoQuery.Delete* or just OrmLite’s db.Delete* APIs explicitly but if you want to AutoQuery Audit History you’d need to call the AutoQuery APIs.
The complexity of the cascading deletes makes it difficult to use the AutoQuery,Delete command; especially, when I already have a proven stored procedure.
So, to clarify, is there no way to use AutoQuery to execute a stored procedure and thus obtain the audit information? If so, it must mean calls to stored procedures( i.e. that change data) would never be audited, which in turn suggests the audit log is not able to be an event store.
Alternatively, can I write directly to the Audit tables without causing problems?
No AutoQuery doesn’t execute stored procs, it only supports its documented operations. Only whilst those operations are executed are the Audit logs created. i.e. it’s not a magical feature that generates audit entries based on your own db commands.
You can write to the Audit Table but you’ll likely invalidate it from maintaining a Full Executable Audit History (i.e. replay all operations to recreate DB state) but you may not ever need that attribute (requires all operations on that table to go through AutoQuery) in which case it’s ok to add your own entries. I’d still populate all applicable fields so it doesn’t break UIs assuming them all to be populated.