Deleted records

Question

Question

Rochdi Badis · Jul 14, 2022

Hi Guys,

is there a way to get an audit or a record of deleted records in a class?

so basically, I'm looking for way to track delete record or from which class or class method call that delete statement was executed, my understanding is that so for example using %OnfterDelete I can track any deleted record using %Delete(), so if the user execute a (Delete from myclass) sql statement can OnfterDelete track that ?

the reason why, is that we have a class where records keeps disappearing (delete) from it and we don't know where or how is this happening, in our software we have 5 places where we delete records from this class but we did put tracking code on them and none of these 5 places has been executed but records keeps getting deleted, so is there a way or piece of code that we can add to our class that can checks when or which call is deleting records?

Thanks

Product version: Ensemble 2014.1

Discussion (6)2

Log in or sign up to continue

Robert Cemper · Jul 14, 2022

and of course any TRIGGER on DELETE will give you more options
e.g. LOG^%ETN() to dump the full call stack

0 0

Eduard Lebedyuk · Jul 14, 2022

Create a unified DELETE trigger (foreach row/object). It would catch both SQL and Object access.

That said I usually advise against hard delete. In virtually all cases soft delete is better.

You soft delete by creating a new property, usually a DeletedOn timestamp. If it's empty then the record is not deleted.

Deletion now consists of setting DeletedOn property to a soft deletion timestamp.

As an additional precaution you can add a BEFORE DELETE trigger which always errors out, forbidding hard deletions. It would save you from every delete except for global kill.

0 0

score 0 · Answer 1 · 2022-07-14T05:11:49-04:00

Robert Cemper · Jul 14, 2022

Take a look to Synchronize Data with DSTIME
Besides other stuff it logs also any DELETE

0 0

score 0 · Answer 2 · 2022-07-14T05:21:29-04:00

Hello,
Have you ever checked the contents of the database journal files?

You should find "Kill" related to these globals entries.

This might give you some clues without code edition.

score 0 · Answer 3 · 2022-07-14T10:31:27-04:00

I have an example of a generic logging filter that takes this approach here: https://github.com/bdeboe/isc-sql-utils#generic-table-logging

score 0 · Answer 4 · 2022-07-14T09:54:38-04:00

Adding a soft delete is a good idea, but then indices will have to be changed as well to support that.

If all your 5 places of code are not called, and record are keeping "disappear" then it might be a SQL that is run by a user or developer. I would recommend to :

- have an detailed audit on that table/class to see it's deletions
- check all ODBC/JDBC users - to see if permissions for delete can be removed
- Possibly to have a code to scan journal files, to find that class, global, pid, date time stamp - and store this on a sperate table or global that can be later examined