How to identify a user of concurrent users in centralized sql server database

[PreethiParkavi]

Dear Friends,
We are developing an inventory maintenance project in C#.net 2005 with MSDE as back end.Data can be accessed by multiuser at the same time, because they are from a single centralized database.
Now, we are planning to implement audit trail to track which record is manipulated by which user.

[ck9663]

Dear Friends,
We are developing an inventory maintenance project in C#.net 2005 with MSDE as back end.Data can be accessed by multiuser at the same time, because they are from a single centralized database.
Now, we are planning to implement audit trail to track which record is manipulated by which user.

depending on some factors. your authentication method, your system design, your entire system architecture...

you can use the system_user, user_name() system variable and function, you can either create a stored proc that update your audit table...or create a trigger that update your audit table every time there's an update on your transaction table...or if you create a before and after update record on your transaction table, you can just add an UpdatedBy field and update it everytime..

-- CK

[PreethiParkavi]

Thank You CK.I thought ,As you say ,It is good to have "UpdatedBy" field in every tables which are going to be audited.Before implementing this, I am having doubt,
Is it possible to pass argument to a trigger which will resolve this problem with less effort from client program?.please advice me.

[ck9663]

Thank You CK.I thought ,As you say ,It is good to have "UpdatedBy" field in every tables which are going to be audited.Before implementing this, I am having doubt,
Is it possible to pass argument to a trigger which will resolve this problem with less effort from client program?.please advice me.

I don't think you can. But there could be other ways of doing it. What argument are you trying to pass and what is it for?

-- ck

[PreethiParkavi]

Thank you CK for your helps.
I am thinking of passing log in user name (which is in "Employee" table) as argument.with this, we can insert the audit log which record is manipulated by which user.How to we accomplish this through triggers/any other T-SQL code ?

Thanks,
Preethi

[ck9663]

Thank you CK for your helps.
I am thinking of passing log in user name (which is in "Employee" table) as argument.with this, we can insert the audit log which record is manipulated by which user.How to we accomplish this through triggers/any other T-SQL code ?

Thanks,
Preethi

so the login profile (username, password) is on the Employee table? i would assume you're using some sort of front-end application. you can keep the employeeid on the application side. during insert, you have call a stored procedure that grabs other info on your employee table that you want inserted on your transaction table, then you do the insert..

or........

you can actually create an updatable view. and update all the table as necessary.

-- ck

[PreethiParkavi]

Hi CK,
You are right in that I am using Front-End application which holds logged user details.I Could not understand the "Updateable View" concept.Could you explain it for me to implement audit-log.

Thanks with Anticipation,
Preethi

[ck9663]

Hi CK,
You are right in that I am using Front-End application which holds logged user details.I Could not understand the "Updateable View" concept.Could you explain it for me to implement audit-log.

Thanks with Anticipation,
Preethi

options 1:
1. create an RecordStat, UpdatedBy, UpdatedOn columns on your table.
2. get all the value of the fields of the table you are updating.
3. instead of issuing an UPDATE statement on the updated field, just update RECORDSTAT = 'I' (or 0 or whatever value you like to signify that the status of the record is inactive). this can also be done if the user delete the record from the GUI
4. on your update statement, build it in such a way that it will be a combination of values from your variable and from your GUI. the value from your variable are those you gather from #2 (above). the value from your GUI are those that your user updated.however for Recordstat = 'A', UpdatedBy = ValueFromYourGUIThatHoldsUserLoggedInfo, UpdatedOn = systemdate(). you will now have an almost duplicate record. with the exception of the fields that were updated and the 3 new fields.
5. In all your query, you have to be conscious that only active records has to be chosen. this means you have to add WHERE RECORDSTAT = 'A' on all your query.

challenge: a user can go to sql analyzer and mess up your table and no audit will be done


option 2:
1. create an RecordStat, UpdatedBy, UpdatedOn columns on your table.
2. create an update trigger on your table
2.a. when update trigger fires, insert the entire deleted table on your table with the exception of RecordStat values of these fields should be RecordStat = 'I'.
3. you should have an almost identical record with the exception of the three fields.
4. In all your query, you have to be conscious that only active records has to be chosen. this means you have to add WHERE RECORDSTAT = 'A' on all your query.

challenge: lot of programming work; disk space (but storage are cheap)


option 3:
1. create an update and delete trigger on your table.
2. when update or delete trigger fires, insert the deleted table into a "dump" table where you dump all updates and deletes. before inserting, make sure to capture the UpdatedBy and UpdatedOn values.
3. this way, you don't change any other queries, you keep your table as is, and you have the audit somewhere else. you can use the audit table for analysis

challenge: lot of programming work; disk space (but storage are cheap); your table is not really complete and has to be split into two


it would all depend on how big your table is. this could all be fine if you have small size. for bigger (read: million-s), try option 3. try all and check for performance.

views
triggers

hope that make sense :)

-- ck

[PreethiParkavi]

Thank You to you CK for spending your valuable time for me. I am trying to implement the third option with triggers and updated field and ask your help if I needed.

With Thanks ,
Preethi.