February 19, 2009 at 12:24 pm
I have the following audit fields in all of my tables.
1. enteredby
2. entereddate
3. updatedby
4. udpateddate
I have the default constraint on these columns so when there was an insert then it would put in the default values.
But, if there was an update then how to log the updatedby & updateddate field.
Whether i have to use update trigger or is there any solution that would do it automatically?
February 19, 2009 at 12:37 pm
Depends. How is the data updated? If it is done using a stored procedure, the stored procedure should be modified to update those fields. If not, then you need an UPDATE trigger to do it.
February 19, 2009 at 12:41 pm
I would typically include the updatedby and updateddate in the update statement, whether it is by ad-hoc sql from the application or in a stored procedure (my preferred and recommended method). You can guarantee you have them set by using a trigger, just be sure the trigger handles set based updates.
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 19, 2009 at 12:43 pm
there are no stored procedure. this is performed by the application. I was wondering if there are any other way to control this in 2005 by the database itself like computed columns & so on....
February 19, 2009 at 12:44 pm
A trigger will also only work if it can get the "updateby" data from somewhere. That's fine if you're using different SQL logins for each user, but not if you're using a web page with connection sharing and that kind of thing.
- 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 19, 2009 at 12:45 pm
i dont want to be handled by application or user as there are possibilities some users may not know in the future they have to update the audit fields hence i wanted this to be handled by the database itself.
February 19, 2009 at 12:50 pm
How are users connected to the database? Is it something that can feed their username to the server?
- 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 19, 2009 at 12:58 pm
the application cannot pass any such values (the application is already built & we just do configuration). it default to getdate(), & current sql user used to update the record.
February 19, 2009 at 1:05 pm
Is this web-based or forms-based? In other words, does each user have a unique connection to the databasse or not.
February 19, 2009 at 1:11 pm
it is web-based & each user doesn't have separate connections. they use a shared connection string but each user would be given a separate username (application specific & not sql server). our intent is to capture the date the record updated & by which sql login.
February 19, 2009 at 1:13 pm
RJ (2/19/2009)
it is web-based & each user doesn't have separate connections. they use a shared connection string but each user would be given a separate username (application specific & not sql server). our intent is to capture the date the record updated & by which sql login.
That won't tell you which user did something. It will be the same for all users. In that case, why even capture it?
- 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 19, 2009 at 1:14 pm
Is the application specific username passed back to SQL Server as part of the insert/update process?
Are these direct updates to the database, or done using stored procedures?
(Sorry if you already answered these, but I'm just trying to get a handle on your request.)
February 19, 2009 at 1:58 pm
1. first place the database design was done to have these fields, at this moment we are not in a position to re-evaluate it hence I'm trying to find a solution wherein we can capture these audit values in all tables.
2. moreover, we are also creating history tables to push whatever values inserted/updated/deleted into that tables with the type of operations performed (I/U/D) but the source for this history table is from the main table so the only value different from main table would be the type of operations.
3. there are some places the application can't handle some functions & which is not built yet. so few inserts/updates/deletes are performed manually by a DBA or by some users using direct SQL statements. So our audit columns would help us in this point.
These missing functinality would be incorporated in future development after we are in production.
February 19, 2009 at 2:23 pm
it is web-based & each user doesn't have separate connections. they use a shared connection string but each user would be given a separate username (application specific & not sql server). our intent is to capture the date the record updated & by which sql login.
If there is a shared sql login then all you would get from the trigger is the shared sql login. Thus your application would need to pass the application user name to the update procedure and include that in the update statement.
In my opinion, you would be best served, if possible, to only allow data manipulation using stored procedures which should require that user name be passed as a parameter. For the odd time a DBA would issue a direct update that person should know that the updatedby and updateddate columns are required.
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 19, 2009 at 3:35 pm
RJ (2/19/2009)
1. first place the database design was done to have these fields, at this moment we are not in a position to re-evaluate it hence I'm trying to find a solution wherein we can capture these audit values in all tables.2. moreover, we are also creating history tables to push whatever values inserted/updated/deleted into that tables with the type of operations performed (I/U/D) but the source for this history table is from the main table so the only value different from main table would be the type of operations.
3. there are some places the application can't handle some functions & which is not built yet. so few inserts/updates/deletes are performed manually by a DBA or by some users using direct SQL statements. So our audit columns would help us in this point.
These missing functinality would be incorporated in future development after we are in production.
You can include in a trigger the "user" value. It'll give you the login that the web page uses, unless that login is part of the sysadmin group, in which case it will give you "dbo".
The trigger would look something like this:
create trigger MyTable_UpdateTrigger on dbo.MyTable
after update
as
update MyTable
set updateby = user
from dbo.MyTable
inner join inserted
on MyTable.ID = inserted.ID;
If you do that, you'll get the value for every update. It will probably only have two values, the login used by the web application and shared by all the users, and "dbo", when a DBA does an update directly, but at least it will have those two.
With your situation, and an inability to modify the application at all, I don't think you're going to get anything more than that. You can't get data out of a system that you don't put into it. That's simple laws of thermodynamics.
- 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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply