Pass a value to a trigger...

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  October 12, 2006 at 6:36 am

  #665451

  Notwithstanding what has already been said

  Top of the head answer

  Create a permanent table consisting of SPID and Username

  In SP

  Insert @@SPID and Username into table

  In Trigger

  Get Username from table using @@SPID (or use SYSTEM_USER if not found to cater for EM/QA updates)

  Delete record from table

  Far away is close at hand in the images of elsewhere.
  Anon.

• Vasc

  SSCarpal Tunnel

  Points: 4643

  More actions

  October 12, 2006 at 7:23 am

  #665470

  If you have a session/connection for each user that connects to your client app you could use

  SET CONTEXT_INFO to store/retrieve information regarding your connection

   

  ---

  Kindest Regards,

  Vasc

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  October 12, 2006 at 7:36 am

  #665483

  I was leaning towards the previously suggested idea of some sort of permanent table for this and then I started thinking "spid" and then David stole my thunder (great minds think alike).  I like David's solution, a lot...

  I do get the jitters when someone tries to use system tables like SysProcesses... it's not that bad but if Developers don't understand how important they are to the very fiber of the Server, they can sometimes cause inadvertant locks on those tables and screw everyone up.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  October 12, 2006 at 7:42 am

  #665487

  David stole my thunder (great minds think alike)

  

   

  Far away is close at hand in the images of elsewhere.
  Anon.

• Peter-218449

  Mr or Mrs. 500

  Points: 557

  More actions

  October 12, 2006 at 7:55 am

  #665495

  Thank you all for the comments.  Your responses have been very helpful.  I am going to look into the permanent table idea because I would rather use triggers instead of stored procedures to update my audit table.

  This is mainly due to one particular requirement.  When the app is shipped the audit trail will not be on. The customer can "turn on" the audit trail at any time. If I were using procedures I would need to first check if the audit trail was on. Then in the case of an update, I'd need to check what values had changed before I made any modification to the audit table.  With 100+ sp's this would be a real pain. 

  Thanks again.  If I have problems I'll be sure to post here.

  Pete