Hello from Portugal.
I manage a database for a customer, and this database has been developed and used from a fistful of developers and many programs, since it is living for about 209 years now.
This database has a main table, where many information are in. We now have faced the fact, that anything is happening from time to time (sometimes once in two weeks, sometimes twice in an hour), which deletes all entries of a specific type. We clearly need to know, what happens. What I did so far: I placed a trigger onto this table, which notices this action, sends an email and then rolls back. So at least the records won't be deleted. My hope was, that if we immediately know, that it happened, we can ask the users what they did the last minute and if they had an error (due to the rollback and error-throwing). It comes as it was clear it would - nobody did anything or noticed anything strange. To just get the username will not help, since many users/connections use the same user (most parts running from within an IIS). So I thought of getting as much information from within this trigger as possible. What command forced the trigger? Maybe a stacktrace? Maybe the id of the connection and any information connected to this id? Anything? Everything?
Normally it could be enough to just prevent this deleting, but I want to know who does what to force this accidently? Because noone knows, what more traps are around...