February 17, 2012 at 4:32 pm
Hey guys,
I am a very new newbie to SQL Server Express 2008 (I came from Access), and I would like to change that database over to SQL Server.
The feature that I'm having an issue moving over is keeping an audit of who adds which record.
Every table that I have has an "AddedBy" field that I would like to populate when a user adds a record, but I can't figure out how to get the trigger written to work.
For example trying to add it to a "tblContactInfo".... any directions on how to write it?
Thanks a ton,
Nick
February 18, 2012 at 12:03 am
http://msdn.microsoft.com/en-us/library/ms189799.aspx
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
--add your code here
GO
Be very careful with triggers. They can often lead to problems. In 2008, there are some other options, such as CDC (Change Data Capture).
February 18, 2012 at 5:21 pm
What should I use then for this situation? I'm sorry, I'm just so sad that I can't assign a global variable for the username and group then have it automatically add that to each new record.
Here is what I am trying to do (to help answer my question)
Every table has a field for "AddedBy" and "MyGroup" I would to have that automatically populate every time a record is added. I use these fields to sort info for queries and reports.
I will look into CDC as well.
Thanks,
Nick
February 19, 2012 at 6:21 am
Test either of these to chose the one you realy want.
SELECT USER_NAME();
GO
SELECT SUSER_SNAME();
Here is an example of its use:
CREATE TABLE #Orders(
[UPC] [varchar](50) NOT NULL,
[SuggestedPrice] [money] NULL,
[Addedby] [Varchar] (45))
CREATE PROC AddToOrders
@UPC VARCHAR(50),
@Price MONEY
AS
DECLARE @Whom VARCHAR(45)
SET @Whom = (SELECT USER_NAME())
INSERT INTO #Orders(UPC,SuggestedPrice,Addedby)
VALUES(@UPC,@Price,@Whom)
Executing the SP as AddToOrders 'xyz',50.00
resulted in:
UPCSuggestedPrice Addedby
xyz 50.00 dbo
February 20, 2012 at 1:34 am
You don't necessary need a trigger for that. A SQL server has a built-in function that returns the identifier on the current connection:
SELECT suser_sname()
returns this identifier in the form:
DomainName\UserName
You can use this function as the default value of your column.
February 20, 2012 at 5:00 pm
Your basic question seems to be how to get the statement in a trigger to work, so I will try to answer that.
In a trigger, you have access to two special tables, INSERTED and DELETED. Rows in those tables represent the data that has been modified. For INSERT, there will be rows in INSERTED but not in DELETED; for UPDATE, there will be rows in both INSERTED (the "after" image) and DELETED (the "before" image); for DELETE, there will be rows in DELETED only. You can use these tables just like any other table to access your modified records.
It is not clear whether "AddedBy" is supposed to be for inserted records only or also changed when an UPDATE is applied to the row. The column default described by Rene above works, but also allows a user to override the value. If you want to control the value, a trigger can not be overridded by a user by specifying a column value.
Here is an example of how to use the INSERTED table to set a column value in your table for rows that have been either inserted or updated.
UPDATEtblContactInfo
SETAddedBy = SYSTEM_USER
FROMtblContactInfo u
JOINInserted i
ONi.{{KeyField}} = u.{{KeyField}}
If you want to apply the trigger on INSERTS only, you could either specify the trigger for INSERT only, or you could JOIN to DELETED to be sure the row is not there (meaning an INSERT rather than an UPDATE).
Hopefully this is enough to get you started on triggers. Let us know if you need more direction.
February 21, 2012 at 10:18 am
Just to reinforce what has already been said. You really don't want a trigger for this. Your best bet is to add a Default constraint (http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx) to the Column using SYSTEM_USER or one SUSER_NAME. Your second best choice is to modify the application to pass in the user name to the insert statement.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 22, 2012 at 7:26 am
Jack Corbett (2/21/2012)
You really don't want a trigger for this. Your best bet is to add a Default constraint ...
Jack, I disagree that the best bet is a default. We need to know what the goal is before determining the best option. If the goal is to know who added a row, a default is probably not the best option. A default simply assures that the column will have a value but it does not assure a correct value.
With a default constraint, a user could enter the name of another user so it would appear that someone else made the change. For example, John is adding a row and could specify "Betty" for the [AddedBy] column. Because a value was specified for the column, the default does not apply. Now the database shows that "Betty" added the row, which is incorrect.
A trigger would prevent that from happening. When John adds the row with "Betty" for [AddedBy], the trigger would overwrite that and put "John" in the AddedBy column, giving correct data for who added the row. This, of course, assumes that users are connecting with their own credentials and do not have the ability to disable triggers.
February 22, 2012 at 7:38 am
fahey.jonathan (2/22/2012)
Jack Corbett (2/21/2012)
You really don't want a trigger for this. Your best bet is to add a Default constraint ...Jack, I disagree that the best bet is a default. We need to know what the goal is before determining the best option. If the goal is to know who added a row, a default is probably not the best option. A default simply assures that the column will have a value but it does not assure a correct value.
With a default constraint, a user could enter the name of another user so it would appear that someone else made the change. For example, John is adding a row and could specify "Betty" for the [AddedBy] column. Because a value was specified for the column, the default does not apply. Now the database shows that "Betty" added the row, which is incorrect.
A trigger would prevent that from happening. When John adds the row with "Betty" for [AddedBy], the trigger would overwrite that and put "John" in the AddedBy column, giving correct data for who added the row. This, of course, assumes that users are connecting with their own credentials and do not have the ability to disable triggers.
It's okay to disagree, but you are assuming that the application is presenting the user the opportunity to directly edit the AddedBy column. My experience has shown that a column like this is typically not presented to the user as editable by the application. In this case a DEFAULT constraint, assuming that the user is connected with their credentials, is, in my opinion, the best option because it guarantees a value and avoids the performance and maintenance overhead of a trigger.
If users are connecting to SQL Server using a shared application account then you are really limited to the second option I proposed which is to have the application pass the user name to the INSERT statement and I'd use the function for that provided by the programming language used, and not present the column as an editable column to the user.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 22, 2012 at 8:13 am
I agree with your assessment of my assumptions. In our applications, the application has a database login and users are credentialed against a user table. That means that the "system user" functions all return the same value (the application's credentials), so we never use a trigger. The applications always pass the "ChangeUser" field to the database. The ChangeUser field is non-nullable. We don't use a column default so that the application will get an error if the ChangeUser is not specified; hopefully the developer will pick up on this before releasing the program. Users cannot connect to the database using their application credentials because they are not SQL Server credentials, they are stored in a table in the database.
It seems that if users are using their own SQL Server credentials to access the database inside an application, they would be able to do so outside the application as well. Users could take their application credentials and connect directly to the SQL Server. In that case, they can do whatever they want outside the constraints of the application; the application would not be preventing them from specifying the "AddedBy" column.
February 22, 2012 at 8:55 am
Default constraints and triggers alike will both fail to record the correct data if you're using a web front-end that uses things like shared connections, et al.
They will ONLY work if each person connecting to the database is doing so with a separate SQL credential, or via an Active Directory domain account.
If the application manages the connection, that's often not an option.
What you need to do in those circumstances is add the login (username) data to the parameters of stored procedures used for your data operations. These can either be stored procedures in the database layer, or LINQ or other objects in your application code or data access layer. Either way, the application will need to provide the data explicitly to the database, if connections aren't individualized in the database engine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2012 at 8:58 am
fahey.jonathan (2/22/2012)
It seems that if users are using their own SQL Server credentials to access the database inside an application, they would be able to do so outside the application as well. Users could take their application credentials and connect directly to the SQL Server. In that case, they can do whatever they want outside the constraints of the application; the application would not be preventing them from specifying the "AddedBy" column.
Yup, they would be able to do that, which is why I always recommend as a best practice for security, that applications use stored procedures to modify data and users are never given direct edit rights (insert/update/delete) to the tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply