Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-11-30 : 11:12:53
|
| I'm in a situation where (for a while) I can't guarantee that certain power users wont be updating (or trying to update) certain tables directly (through access maybe or some other rogue application). I can't take their permissions away (at this stage). I can't stop them from performing their business processes. But I can get them to slightly change their queries. But I want to guarantee that it happened.What I want to be able to do is prove who changed what when. So my trigger is putting the changes in an audit table just fine. Because all the code that my team write is through SProcs and the SProcs require that you tell it who the current user calling the SProc is, we don't have a problem there. The problem comes with the scenario mentioned at the outset.In a trigger there is the UPDATE keyword that you can use in the WHERE clause. But what I'm after is more of a PROVIDED keyword/functionality. From inside the trigger, I want to force all code that does an update to provide a value for the EditByID column. Can that be done? |
|
|
cr8nk
Yak Posting Veteran
77 Posts |
Posted - 2007-11-30 : 14:56:44
|
| Bob,If you are in a situation where people are connecting to the database with windows auth or individual sql server logins you should be able to handle it fairly easy by using the suser_sname() function. However if they are using an application which uses a shared login then hopefully everyone has to log into that application with a unique user id. You would need to pass that user id into every query or proc that modifies data so you can reconcile it back to which user made changes, otherwise you can't distinguish who made the change. It all depends on how people are connecting and the queries they are running. |
 |
|
|
|
|
|
Force behaviour