So some kind of login / logout table to show which users are in and out of the database? And then match up the list with the datetime of the deletions? Yeah, that could provide a clue. I'll ask my contact if that might work, not sure if everyone leaves the app up all day or they go in / out (only 4 users) Karen -----Original Message----- From: [email protected] <[email protected]> To: RBASE-L <[email protected]> Sent: Tue, Mar 15, 2022 9:06 am Subject: Re: [RBASE-L] - Delete Trigger mystery?
But you also know that when the deletion is limited to EXACT rows and not random gaps in the data file, that the deletions are occurring from one of two sources. Programmatically or direct User. That was why one of my suggestions was to add a table with a datetime (and any other info you might feel germane ) and in a delete trigger have that table updated with the timestamp so it might point to a time period when certain users were in the db, or at some time when no one should be. On Monday, March 14, 2022 at 5:58:29 PM UTC-4 Karen Tellef wrote: But we have NO CLUE where/when these deletions are happening. There is nowhere in code that I can add the insertion of a delete flag, because there honestly should be nowhere that a user (nor a program) is deleting these rows. This data has to do with lawsuits, and the data has to stay there until the lawsuit is settled and then the record is archived at monthend. We know someone isn't running this monthend routine when they shouldn't, because a bunch of other stuff would also happen at the same time, and none of that is happening Karen -----Original Message----- From: [email protected] <[email protected]> To: RBASE-L <[email protected]> Sent: Mon, Mar 14, 2022 3:23 pm Subject: Re: [RBASE-L] - Delete Trigger mystery? A "Delete Flag" is just a single character field that excludes the record from any results. It is in lieu of "Actual" deletion. Internally, I believe R:Base does the same thing with deleted records and they stay "so marked" until PACK or RELOAD occurs. By doing this, you can then restrict to a single event that would occur ONLY when one person, so authorized, has access to do so. The requirement for a password in advance of any deletions is a good stopgap too, so long as the password is not widely known, but you know a secret is only a secret when kept among 3 people and two of them are dead. On Monday, March 14, 2022 at 2:34:53 PM UTC-4 Karen Tellef wrote: Do you mean using GRANT to restrict deletion? You know, I have never once used GRANT in all the decades I've used RBase!!! I'd have to read up on how to use it. Do you assign permission to delete to only specific users? And what "name" is it using to assign permissions? Stuff like that that I don't have the answers to, would have to read up on. Also, I have nowhere in my code where the user is allowed to delete a record, so there wouldn't be anywhere to set some kind of "delete flag". No program deletes a batch of records other than the one monthend program which works fine to archive/delete records, we know that one works on the last day of the month as intended. Only one user has the password to run that routine and the monthend date is never the one that is erroneously deleting records. Karen -----Original Message----- From: [email protected] <[email protected]> To: RBASE-L <[email protected]> Sent: Sun, Mar 13, 2022 9:04 am Subject: Re: [RBASE-L] - Delete Trigger mystery? Well it might take a little work, but this is what I would do. #1 - I would lock the table down with access control as tight as practically possible. Add a single character field to the table to use as a Delete Flag (hangover from the old DBase days, using an asterisk (0x2A) as the marker or something else as your choosing). Having done that, you would have to change your code to exclude those marked as deleted. To the above, you might also add another table with just a datetime column that is added by a trigger from the main table that is updated ANY TIME the table is accessed (this will give you a timeframe to point to user activity involvement at the time of ANY access or deletions) Add a second table, as you have already done to contain just the records marked as deleted. This would be to determine if there are any actual deletions occurring in either table and if there are any deletions, is there any commonality to the deletions. It seems as the detective work is pointing to user access being the culprit, but maybe by taking extraordinary steps, it can be conclusive one way or another. On Saturday, March 12, 2022 at 2:45:33 PM UTC-5 Karen Tellef wrote: Adrian: I am assuming they were actually deleted. Since the trigger is "before delete" and operates as it should other than giving me that null DeletedBy, and I can't find them anywhere in the original table, I have to think they were deleted. If there was an issue with indexes or a database problem itself, I would think it would show up on an Autochk. This database has never had a bad Autochk. Karen -----Original Message----- From: Adrian Huessy <[email protected]> To: [email protected] <[email protected]> Sent: Sat, Mar 12, 2022 11:14 am Subject: RE: [RBASE-L] - Delete Trigger mystery? Karen,Quick question: do you feel that records are/were effectively deleted or do you have NULL entries even though nothing was deleted?BR, Adrian Von: 'Karen Tellef' via RBASE-L [mailto:[email protected]] Gesendet: Samstag, 12. März 2022 01:02 An: [email protected] Betreff: [RBASE-L] - Delete Trigger mystery? Why I needed the before-delete trigger: no one should ever be deleting data from this table. And although they have the full version of RBase, no one knows how to "get to the R> prompt". There is a monthend routine that the senior user runs that will delete a batch of data, but that's all (we remove the trigger before this monthend routine, then put it back on) Yet every now and then we find data mysteriously disappeared. So I created a before-delete trigger. It takes the record to be deleted and appends it to an archive table. 3 columns in the archive table will hold the deleted date / time and the user who deleted it. Works perfectly at my development environment and when I test it at the place where the database is installed. Records the date, time and the user. However, twice now we have found records in that archive table that had been deleted (hundreds at a time). The records had the deleted date/time but had NO user name. Here's the code I use in my stored procedure. The DeletedDate and DeletedTime works fine, it gets updated every time. There is no login to this app, so I grab the NetUser (used many times in the application for other things, successfully). The first time that the archive had no user name, I modified the stored procedure to grab the ComputerName. But still, nothing..... SET VAR vText TEXT = NULL SET VAR vText = (CVAL("NetUser")) IF vText IS NULL THEN SET VAR vText = (CVAL("ComputerName")) ENDIF UPDATE ClaimsDeleted SET DeletedDate = .#DATE, DeletedTime = .#TIME, + DeletedBy = .vText WHERE claim = .spClaim Can anyone think of anything else I can trap that would help me figure out how/when the records got deleted? Or does anyone know how records could get deleted when there actually is no NetUser or ComputerName? Karen -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/1654695576.980450.1647043308102%40mail.yahoo.com.-- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/dbba74c3572c4f6aa054b424fd42d70b%40huessy.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/ff8ce79d-ce09-43b8-a3fc-48a36de02ca2n%40googlegroups.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/bc0980d9-c3a2-47ee-8d40-b1364fb952c7n%40googlegroups.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/f7c59574-ff16-48d1-86d0-43df117ecbf5n%40googlegroups.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/1156418030.1738749.1647353471074%40mail.yahoo.com.
