Audit Tables for Many-Many Relationships
-
We're using Entity Framework 4, ASP.NET MVC 4 and SQL Server 2008 R2. We have some join tables for several entities, for many-many relationships. I'm working to create audit tables/data for each of these relationships/join tables. So far in my research, I've found that triggers are common. We use Windows Authentication, and we have a User table that stores authorized domain users. For our audit tables, we'd like to know which user modified the relationship. The problem I have is, how do we get the ID from our User table based on the user who updated a relationship using our MVC application? I've looked at some options, and the only thing I can come up with so far, in a trigger, is this:
SELECT @username = nt_username FROM sys.sysprocesses WHERE spid = @@SPID
That will retrieve my username without the domain, which is what I want. I can then compare it to our User table and get the ID where the username matches. But is this the best way to go about it?
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
We're using Entity Framework 4, ASP.NET MVC 4 and SQL Server 2008 R2. We have some join tables for several entities, for many-many relationships. I'm working to create audit tables/data for each of these relationships/join tables. So far in my research, I've found that triggers are common. We use Windows Authentication, and we have a User table that stores authorized domain users. For our audit tables, we'd like to know which user modified the relationship. The problem I have is, how do we get the ID from our User table based on the user who updated a relationship using our MVC application? I've looked at some options, and the only thing I can come up with so far, in a trigger, is this:
SELECT @username = nt_username FROM sys.sysprocesses WHERE spid = @@SPID
That will retrieve my username without the domain, which is what I want. I can then compare it to our User table and get the ID where the username matches. But is this the best way to go about it?
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
Matt U. wrote:
But is this the best way to go about it?
Compared to which other way? I like triggers; it'll do what it is supposed to do without having to do anything special in the calling code. The nt_username[^] field from the current process will always be the Windows user name, so that would look correctly too. IIRC then the variable "SYSTEM_USER" should return the same. There's two or three alternative approaches that come to mind, each of them not as good as triggers.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Matt U. wrote:
But is this the best way to go about it?
Compared to which other way? I like triggers; it'll do what it is supposed to do without having to do anything special in the calling code. The nt_username[^] field from the current process will always be the Windows user name, so that would look correctly too. IIRC then the variable "SYSTEM_USER" should return the same. There's two or three alternative approaches that come to mind, each of them not as good as triggers.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Thanks for the info! I've been working to put it together using triggers and I can't imagine any way that would be simpler. I think I'll just go that route. I only asked because I'm not entirely familiar with triggers, setting up audit tables, and so on.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
We're using Entity Framework 4, ASP.NET MVC 4 and SQL Server 2008 R2. We have some join tables for several entities, for many-many relationships. I'm working to create audit tables/data for each of these relationships/join tables. So far in my research, I've found that triggers are common. We use Windows Authentication, and we have a User table that stores authorized domain users. For our audit tables, we'd like to know which user modified the relationship. The problem I have is, how do we get the ID from our User table based on the user who updated a relationship using our MVC application? I've looked at some options, and the only thing I can come up with so far, in a trigger, is this:
SELECT @username = nt_username FROM sys.sysprocesses WHERE spid = @@SPID
That will retrieve my username without the domain, which is what I want. I can then compare it to our User table and get the ID where the username matches. But is this the best way to go about it?
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
As much as I loathe triggers spit audit is the one area where they are the right tool for the job. We have a Modified and ModifiedBy field on EVERY table and use that in the trigger to identify the culprit. We also do not use EF or windows authentication, our apps use app specific credentials to connect to the database.
Never underestimate the power of human stupidity RAH
-
Thanks for the info! I've been working to put it together using triggers and I can't imagine any way that would be simpler. I think I'll just go that route. I only asked because I'm not entirely familiar with triggers, setting up audit tables, and so on.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.