I'm updating an audit/log table that is populated by trigger when a table is updated. My intention is to use the sys.objects.object_id for table name and column name columns in the log table but it's not possible to use a FK reference for the system views. Is there any useful alternative to maintain referential integrity on the table?

  • 1
    For what purpose? To prevent someone from dropping and recreating the table? What if there is a valid reason to do that? The fact that you logged some information about a table shouldn't lock that table into existence forever. – Aaron Bertrand Aug 27 at 2:57
  • Fair point. I've generally worked from the principle that the database should, where practical, make it difficult for users to do stupid things. Inserting or updating a row with incorrect data when that can be prevented would be one of those situations but in this case the table should only be updated by trigger so the data should be correct. – David Clarke Aug 27 at 3:10
  • Are you letting users supply the object_id to your log table? (Also tangential but maybe you should consider using the name instead of the id, or both, depending on whether it is more likely for an object to get dropped or renamed.) – Aaron Bertrand Aug 27 at 3:19
  • 1
    Well, even if storing the name is the straw that breaks your storage, you could store the object_id + name combination once in another table, the first time you see each combination. Then your log table could store a surrogate and for the first 2 billion entries you could benefit from less storage due to data / columnstore compression. (I'm not trying to design your system, I'm just suggesting that losing information because "it takes too much storage" is not an unsolvable problem.) – Aaron Bertrand Aug 27 at 3:32
  • 1
    The other thing about sitting on 2008R2 forever is that, while you're unlikely to come across any functional bugs that haven't been addressed in service packs long ago, you stop getting security fixes. That risk isn't a tangible line item on your budget, but it's not free. And if you need to open a case with Microsoft (say, for a stack dump, or corruption, or anything else your team can't handle), you are completely out of luck. Either of those scenarios can lead to a cost that is a lot higher than a license. – Aaron Bertrand Aug 27 at 13:43

You could use a trigger on the log table that rolls back when the object_id being inserted is not found in sys.objects - since triggers are the typical mechanism we use when a foreign key doesn't make sense or isn't possible (like maintaining integrity across database or instance boundaries).

But do you really want to lose all of the data because one column represents an object that no longer exists, or never existed? (Maybe that's ok; I don't what you're auditing/logging.) What is the likelihood of this happening since the trigger produces the object_id, and the system isn't known for messing up?

I'm not sure I understand the point anyway; while a foreign key to sys.objects would prevent someone from logging information about an object that doesn't exist, the presence of just one row in the logging table would make it impossible to ever drop that object. (Maybe that's ok too, but it doesn't sound wise.)

If the fear is that someday a user reviewing log history will hit a dead end because the object has since been dropped, a trigger that made sure the object existed when the row was inserted isn't going to have saved you from that scenario anyway. You'd need a DDL trigger maybe, on DROP_TABLE, so that either the log history associated with that table is purged, or the object_id is moved somewhere else so that you can still track down what that table used to be.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.