Post reply

Trigger: Need help to save activity log on delete

  • March 28, 2022 at 10:06 pm

    #4007322

    ScottPletcher wrote:

    Michael L John wrote:

    ScottPletcher wrote:

    As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs and FKs would slow down the process even more that having the trigger to begin with and then be further slowed down by writing rows to the audit table.  There is zero reason to ever put FKs on the audit table.

    I disagree, at least in some cases.  In some cases I think it's worth having disabled FKs there as documentation (only).  They give info to readers but don't cause any system overhead, since they are disabled.

    Then what is the point of creating them in the first place?  Creating a FK, disabling it, and using it as a documentation mechanism seems like a very round about way of doing things.

    I stated the reason for creating them - for documentation purposes.

    What else would you suggest to document that the relationship between the two tables exists but to prevent it from having any overhead?

    I suspect that folks trying later to determine the relationships between tables will find it useful.

    A disabled trigger to document your databases.  That's a new one to me.

    For starters, there is not an actual relationship between an audit table and any other table, except for the table being audited.  One could argue that there is no definable relationship between those tables also.

    What happens when the trigger gets enabled? Inadvertent things happen in real life.

    I'm also assuming that there is some sort of naming standard in place. So, again a big assumption, that a column called "User_ID" is universally understood as the primary key of the Users table?

    Before you understand the audit table, you would first need to understand the table being audited.  That is where the documentation belongs.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply